Redshift COPY command returns stl_load_error 1205 Invalid Date Format - length must be 10 or more

Question

I am copying a .csv file from S3 into Redshift and the Redshift COPY command returns

stl_load_error 1205
Invalid Date Format - length must be 10 or more.

My dates are all 10 characters long and in the default 'YYYY-MM-DD' format.

Command:

COPY [table]
FROM [file location]
ACCESS_KEY_ID [___]
SECRET_ACCESS_KEY [____]
FORMAT AS CSV
IGNOREHEADER 1;

The table was created using:

CREATE TABLE finance.commissions_life (
submitted_date date,
campaign varchar(40),
click_id varchar(40),
local_id varchar(40),
num_apps float);

And the .csv is in that exact format as well.

Is anyone else having a similar issue?

Hi Stoddard, did have similar issue. My way was to load data in a staging table making the submitted_date varchar(50). Once loaded, I can control the format when send the data to the final table. Try changing the submitted_date to varchar(50) and see if it works. — zip

John Stark John Stark · Accepted Answer · 2020-01-24T00:29:43

When I have run into this error in the past, I always fall back on explicitly defining both the delimiter to be used, and the date format:

COPY db.schema.table
FROM 's3://bucket/folder/file.csv'
CREDENTIALS 'aws_access_key_id=[access_key];aws_secret_access_key=[secret_access_key]'
DELIMITER AS ','
DATEFORMAT 'YYYY-MM-DD'
IGNOREHEADER 1
;

If you have the ability to alter the S3 file's structure/format, you should explicitly wrap the dates in quotes, and save it as a tab-delimited text file instead of a CSV. If you can do this, your COPY command would then be:

COPY db.schema.table
FROM 's3://bucket/folder/file.csv'
CREDENTIALS 'aws_access_key_id=[access_key];aws_secret_access_key=[secret_access_key]'
DELIMITER AS '\t' 
DATEFORMAT 'YYYY-MM-DD'
IGNOREHEADER 1
REMOVEQUOTES
;

Additionally, you should be able to query the system table stl_load_errors to gather additional information on the exact row/text that is causing the load to fail:

SELECT *
FROM stl_load_errors
ORDER BY starttime DESC
;

Redshift COPY command returns stl_load_error 1205 Invalid Date Format - length must be 10 or more

3 Answers