Redshift COPY Statement Date load error

2
votes

I am loading the data using COPY command.

My Dates are in the following format.

  1. D/MM/YYYY eg. 1/12/2016
  2. DD/MM/YYYY eg. 23/12/2016

My target table data type is DATE. I am getting the following error "Invalid Date Format - length must be 10 or more"

1
pythonamazon-redshift

1 Answers

2
votes

As per the AWS Redshift documentation,

The default date format is YYYY-MM-DD. The default time stamp without time zone (TIMESTAMP) format is YYYY-MM-DD HH:MI:SS.

So, as your date is not in the same format and of different length, you are getting this error. Append the following at the end of your COPY command and it should work.

[[COPY command as you are using right now]] + DATEFORMAT 'DD/MM/YYYY'

Not sure about the single digit case though. You might want to pad the incoming values with a 0 in the beginning to match the format length.