[resolved] update/insert into with date-Object

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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