topic Re: Error while comparing TalendDate with database date column in Talend Studio

Error while comparing TalendDate with database date column

Anonymous — Wed, 08 Mar 2017 07:45:56 GMT

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

Re: Error while comparing TalendDate with database date column

TRF — Wed, 08 Mar 2017 10:14:20 GMT

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

Re: Error while comparing TalendDate with database date column

Anonymous — Wed, 08 Mar 2017 10:17:46 GMT

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

Re: Error while comparing TalendDate with database date column

TRF — Wed, 08 Mar 2017 10:44:43 GMT

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

Re: Error while comparing TalendDate with database date column

Anonymous — Wed, 08 Mar 2017 10:57:01 GMT

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?

Re: Error while comparing TalendDate with database date column

TRF — Wed, 08 Mar 2017 11:08:23 GMT

Try "CAST" when calling the SQL function.

Re: Error while comparing TalendDate with database date column

Anonymous — Wed, 08 Mar 2017 13:49:47 GMT

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