Five Stars

Error while comparing TalendDate with database date column

Hi,
I have my database date columns storing datetime in the format 'YYYY-MM-DD HH:mi:ss:mmm'
I want the talend current date to be returned in this format. So , I used this TalendDate.formatDate("YYYY-mm-dd HH:mm:ss",TalendDate.parseDate("YYYY-mm-dd HH:mm:ss",TalendDate.getDate("YYYY-mm-dd HH:mm:ss")))
The problem is this is converting it to string. Also, I keep getting the error 'Incorrect syntax' when I pass the above to sql. And hence I cannot compare with date column of the database.
Am using sql server
Any thougths on this?
Thanks
Rathi
6 REPLIES
One Star

Re: Error while comparing TalendDate with database date column

Hi,
Get the current Talend date using this syntax:
TalendDate.formatDate("yyyy-MM-dd HH:mm:ss:SSS",TalendDate.getCurrentDate())

Get the date from SQL Server using this syntax in your SQL query:
CONVERT(VARCHAR(24), yourColumnDate, 121)

Check the result on the SQL Server before (I'm not sure for this point).
Now you have 2 strings you can compare.
Hope this helps.
TRF
Five Stars

Re: Error while comparing TalendDate with database date column

Hi TRF, thanks for the response
I need to compare the dates. >= , <= etc. With strings I would not be able to achieve this. Is there any way to do this?
Thanks
Rathi
One Star

Re: Error while comparing TalendDate with database date column

row1.yourTalendDate.compareTo(row2.yourSqlDate)
// return 0, < 0 or > 0 depending of the comparison result
Five Stars

Re: Error while comparing TalendDate with database date column

Thanks TRF. That will work.
I have to pass the talendate as a parameter to a function. The function in sql server only understands data as date. How do I do this?
One Star

Re: Error while comparing TalendDate with database date column

Try "CAST" when calling the SQL function.
Five Stars

Re: Error while comparing TalendDate with database date column

Ok. I have a tsetglobalvar whose value is var = TalendDate.getDate("YYYY-MM-dd HH:mm:ss:mmm").
Now I need to pass this to tmssqlinput. How do I do this? The query in tmssqlinput is "select * from function("+globalMap.var+)"
I am trying to pass with cast and convert, but still keeps giving syntax error
Thanks
Rathi