how do I convert a date to YYYYMMDD?

Question

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?

Answer 1

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

how do I convert a date to YYYYMMDD?

Sign up