One Star

[resolved] update/insert into with date-Object

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
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

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

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
5 REPLIES
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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.
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
One Star

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

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