jo112358 jo112358 - 6 months ago 8
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'
to
20120627
?

I tried:

TO_DATE(Q_RATE_DATE, 'DD-MON-YY', 'YYYYMMDD')


no luck, what am I doing wrong?

jdg jdg
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')
Comments