topic Re: Date Difference in Talend Studio

Date Difference

Karuetl — Thu, 25 Jan 2018 17:43:04 GMT

In the contexts i gave below two variables and values

StDt -> Date -> 01/01/2018

EnDt-> Date -> 01/10/2018

context.vDiff= TalendDate.diffDate(context.StDt,context.EnDt,"dd");
System.out.println(context.vDiff);

i got printed as 0

Re: Date Difference

Anonymous — Thu, 25 Jan 2018 18:13:49 GMT

You're printing vDiff, but setting the result of the date difference to diff. Have you assigned the correct variable?

Re: Date Difference

Karuetl — Thu, 25 Jan 2018 18:16:07 GMT

Sorry it was typo

In the contexts i gave below two variables and values

StDt -> Date -> 01/01/2018

EnDt-> Date -> 01/10/2018

context.vDiff= TalendDate.diffDate(context.StDt,context.EnDt,"dd");
System.out.println(context.vDiff);

i got printed as 0

Re: Date Difference

Karuetl — Thu, 25 Jan 2018 20:20:21 GMT

Figured out that Talend is the date format as 'yyyy-MM-DD hh:mm:ss' , Even though if I give the value as 02/02/2018 as date for the context variable , it is not accepting the value

Got another issue

When i run the teradata query by giving context date variable in the where clause it is throwing error as below .. Reason is Talend is printing value like Fri Feb 20 00:00:00 EST 1981

[FATAL]: kafka.loopjob_0_1.LoopJob - tTeradataInput_1 [Teradata Database] [TeraJDBC 15.10.00.14] [Error 3707] [SQLState 42000] Syntax error, expected something like an 'AS' keyword between the word 'Sat' and the word 'Feb'.
Exception in component tTeradataInput_1
java.sql.SQLException: [Teradata Database] [TeraJDBC 15.10.00.14] [Error 3707] [SQLState 42000] Syntax error, expected something like an 'AS' keyword between the word 'Sat' and the word 'Feb'.
at

Re: Date Difference

Anonymous — Fri, 26 Jan 2018 01:45:11 GMT

Hi Karuetl
Before using the function diffDate, print the context variables to see if the value is assigned correctly.

System.out.println(context.StDt);
System.out.println(context.EnDt);
context.vDiff= TalendDate.diffDate(context.StDt,context.EnDt,"dd");
System.out.println(context.vDiff);

About using context variable with date type in a query, it usually use a single quote to enclose the context varaible, for example:
"select c1, c2 from tableName where dateColumn>'"+context.dateVar+"'"

Let me know if it works.

Regards
Shong