convert MM/DD/YYYY to YYYYMMDD in redshift

Question

I have a requirement to convert MM/DD/YYYY to YYYYMMDD in amazon redshift database.

My result of this query gives me some weird result. Can some one please help me.

select to_date ('07/17/2017','YYYYMMDD');

0007-07-20

Answer 1

If you just wish to convert the hard-coded string into a DATE:

select to_date('07/17/2017', 'MM/DD/YYYY')

If you have a column already formatted as DATE, then use:

to_char(fieldname, 'YYYYMMDD')

Combining the two concepts:

select to_char(to_date('07/17/2017', 'MM/DD/YYYY'), 'YYYYMMDD')