Date Format

[sonalijagtap]

Hi,

I have a context variable(ClaimDate) of type Date.

Whenever I am setting default value of ClaimDate in Context tab as '2018-02-22', it will throw an error at runtime as value is null.

but when I pass the value as '2018-09-20 hh:mm:ss', It is working. but prining the value like 

Thu Sep 20 00:00:00 IST 2018

But I want the value of that variable as '2018-09-20'. I tried to convert using 

context.ReClaimDate=TalendDate.parseDate("yyyy-MM-dd", TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", context.ReClaimDate))

but it is still giving the same output  "Thu Sep 20 00:00:00 IST 2018"

Please help on priority.

Thank You,

Sonali Jagtap

[fdenis]

Date are stored as java Date Type.
if you want to display or to retrieve it, using your own format, you have to convert it to String using TalendDate.formatDate(xxx,xxx)

[sonalijagtap]

But I need that date further in Redshift query like 

Select * from Table where cal_date<context.claimDate

here Cal_date is of Date type with the format 'yyyy-mm-dd'

how to achive this if I convert the date to String as per your suggestion?

[fdenis]

your query must be like
"Select * from Table where cal_date<'" + TalendDate.formatDate("yyyy-MM-dd", context.ReClaimDate)) +"'"

[sonalijagtap]

TalendDate.formatDate() will return the String type value. my cal_dt is date type.

I tried using below query because parseDate will convert the value from String to date.

"SELECT * FROM table
WHERE CAL_DT= '" + TalendDate.parseDate("yyyy-MM-dd", TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", context.ReClaim_date)) +"'"

But I am not getting data from table because 

TalendDate.parseDate("yyyy-MM-dd", TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", context.ReClaim_date)) is returning the date in the format

Wed Dec 06 00:00:00 UTC 2017

Expected Output is: 2017-12-06 not the above highlighted

[fdenis]

SQL query is String.
your database engine is going to analysing your query.
Then it is going to convert String to Date.
remove paseDate.

[TRF · Accepted solution]

As for many database engines, try to use "TO_DATE (string, format)" function.

From my point of view, the easiest solution is to declare the context variable as a String then pass it to TO_DATE in the WHERE clause:

"Select * from Table where cal_date < TO_DATE('" + context.ReClaimDate + "', 'YYYY-MM-dd')"

[sonalijagtap]

to_DATE is working. Actually I tried that earlier but because of some syntax error, it was not working.

Thank You So much.

Sonali