SQL Question
how do I convert a date to YYYYMMDD?
I have two tables with two different date formats and I need to compare them. I am trying to convert the field Q_RATE_DATE; how do I change it from
'27-JUN-12'20120627I tried:
TO_DATE(Q_RATE_DATE, 'DD-MON-YY', 'YYYYMMDD')
no luck, what am I doing wrong?
Answer
To render as text:
TO_CHAR(DATE_FIELD, 'YYYYMMDD')
To reconvert that text back to date:
TO_DATE(TO_CHAR(DATE_FIELD, 'YYYYMMDD'),'YYYYMMDD')
Editing based on Alex Poole's excellent comment. If you find that one date is stored as a string while the other is a date, you may need to use a mix of the above two queries.
To convert the text string into date:
TO_DATE(STR_DATE_FIELD, 'YYYYMMDD')
Source (Stackoverflow)