Converting date format in denodo database

Question

I'm trying to convert value for DIM_DT_ID to MMddYY. I'm successful in doinf that. However, query fails because ultimately I'm comparing a character value to date here. Is there a way by which I can get value for DIM_DT_ID in MMddyy format and its data type still remains DATE ? Here DIM_DT_ID SELECT DIM_DT_ID DIM_DT_ID >= FORMATDATE('MMddyy',ADDDAY(TO_date('yyyy-MM-dd','2016-12-21'), -25)); from abc; Regards, Ajay

Answer 1

In Denodo, to convert a string to a date field, use "to_date()" (which returns a date).

Then, don't convert back to a string, leave that field as a date (so don't use "Formatdate()", which returns a string).

So:

SELECT *
FROM MyTable
WHERE now() >= to_date('yyyy-MM-dd',myStringFieldThatLooksLikeADate)

In my example, "now()" is a date, and so is the output of the to_date function... so you can do a comparison.

If you try to convert the date back to a string using formatdate, it won't work:

#This doesn't work:
SELECT *
FROM MyTable
WHERE now() >= formatdate('MMddyy',to_date('yyyy-MM-dd',myStringFieldThatLooksLikeADate))

It doesn't work because we are comparing a date ("now()") to a string.