topic Re: [resolved] update/insert into with date-Object in Talend Studio

[resolved] update/insert into with date-Object

Anonymous — Sat, 16 Nov 2024 13:43:39 GMT

Hi,
I need to substitute getdate() in some tMssqlRow-components so I can use jdbc-components with oracle and ms sql servers
only by changing the jdbc-url.
At the moment I use a TalendDateformatDate("yyyy-MM-dd hh:mm.ss", TalendDate.getCurrentDate()) but
I get an error telling me that the convert from a char is not possible because it is out of the datetime range of value. (I did not upload a screen because it is in German)
Any solution, I figured out yet, with tJavaRow or similar is not possible.
The query I need later will include an insert into .. select .... therefor I need the date directly in that tJDBCRow.
Kind regards,
Harald

Re: [resolved] update/insert into with date-Object

Anonymous — Wed, 21 Oct 2009 04:29:03 GMT

Hello

I get an error telling me that the convert from a char

What's the data type of data_last column in table? date or datetime?
Please show me the whole error message.
Best regards

shong

Re: [resolved] update/insert into with date-Object

Anonymous — Wed, 21 Oct 2009 07:06:03 GMT

It is a datetime column and the error message is:

Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerhalb des gültigen Bereichs.

translated it would be something like that:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Re: [resolved] update/insert into with date-Object

Anonymous — Wed, 21 Oct 2009 07:25:46 GMT

Hello
You need check that what's the default date format of your database. So now you can try that:
1)
TalendDate.formatDate("yyyy-dd-MM hh:mm.ss", TalendDate.getCurrentDate())
or
TalendDate.formatDate("MM-dd-yyyy hh:mm.ss", TalendDate.getCurrentDate())
2)
Go to windows-->preferences-->Talend-->Internationlization and change the default local language.
Best regards

shong

Re: [resolved] update/insert into with date-Object

Anonymous — Wed, 21 Oct 2009 07:55:53 GMT

Hi,
thank your for your reply.
Regrettably 2) setting the local language does not change anything regarding my problem
and 1) is already correct, the format is ansi sql so yyyy-MM-dd hh:mm:ss.SSS should be correct.
In other parts of the job I can use a tMap to produce the data for a normal tJDBCOutput.
It works with yyyy-MM-dd hh:mm:ss.SSS in that tMap. Only if I enter the date-object directly in the sql-query it does not work.
Kind regards,
Harald

Re: [resolved] update/insert into with date-Object

Anonymous — Wed, 21 Oct 2009 15:08:23 GMT

Hi,
I found a solution:

date_last ={ts '" + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss.SSS",TalendDate.getCurrentDate()) + "'}

the jdbc-escape-sequenz "ts" works, I just need a date-string
Kind regards,
Harald