Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')"
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?
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
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')"
to_DATE is working. Actually I tried that earlier but because of some syntax error, it was not working.
Thank You So much.
Sonali