I have two columns of dates with different formats that need to do a operation in where clause.
1st column DATE1 is like '01Jan2000'
2nd column DATE2 is like ‘01JAN2000:12:00:00'
Now I would like to have all records within one year between DATE1 and DATE2. 0<DATE1-DATE2 and DATE1-DATE2<365
How to write this where clause?
Thank you in advance!
The FORMAT of a DATE is just for display, you can simply compare them.
But you data show a DATE and a TIMESTAMP, so you must add a typecast:
WHERE DATE1 > CAST(DATE2 AS DATE)
AND DATE1 < CAST(DATE2 AS DATE) + 365