topic Re: Date Format in Talend Studio

Date Format

sonalijagtap — Fri, 21 Sep 2018 07:59:40 GMT

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

Re: Date Format

fdenis — Fri, 21 Sep 2018 08:06:22 GMT

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)

Re: Date Format

sonalijagtap — Fri, 21 Sep 2018 08:12:26 GMT

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?

Re: Date Format

fdenis — Fri, 21 Sep 2018 08:25:03 GMT

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

Re: Date Format

sonalijagtap — Fri, 21 Sep 2018 09:06:16 GMT

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

Re: Date Format

fdenis — Fri, 21 Sep 2018 09:13:46 GMT

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

Re: Date Format

TRF — Fri, 21 Sep 2018 09:16:21 GMT

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')"

Re: Date Format

sonalijagtap — Fri, 21 Sep 2018 09:32:29 GMT

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

Thank You So much.

Sonali