Cast Date Column in the Redshift COPY Command

Question

My data has the date in the "02JAN2020" format and I want to load the data using the COPY Command

copy test.Demographics from 's3://xyz-us-east-1/Blu/'
access_key_id ,’Access_Key_ID>’
secret_access_key ’<Secret_Access_Key>’
delimiter ',' dateformat 'auto' 
GZIP;

The column data type is a date but it's still failing. I checked the stl error logs and it's the date formate issue.

I want the value in the column as 2020-01-02 and not 02Jan2020

Answer 1

Specify the date format with

DATEFORMAT 'DDMONYYYY'