I am trying to move some data from a CSV in S3 to a Redshift table. The date column is giving me a hard time however. When opened in a text editor, the date is formatted like this: "YYYY-MM-DD HH:MI:SS", however when I open the file in excel, the formula bar lists the date as YYYY-MM-DD HH:MI:SS AM/PM, and the actual table doesn't even include the seconds, or the AM/PM.

When I run the query in the image, I get this error: Date value did not match format specified [yyyy-MM-dd] And when I remove the DATEFORMAT line entirely, I get: Invalid Date Format - length must be 10 or more.

What am I doing wrong? The column in the table is expecting a date in the format of yyyy-MM-dd, but my boss assured me that the time shouldn't affect that
enter image description here

I am trying to move some data from a CSV in S3 to a Redshift table. The date column is giving me a hard time however. When opened in a text editor, the date is formatted like this: "YYYY-MM-DD HH:MI:SS", however when I open the file in excel, the formula bar lists the date as YYYY-MM-DD HH:MI:SS AM/PM, and the actual table doesn't even include the seconds, or the AM/PM.

When I run the query in the image, I get this error: Date value did not match format specified [yyyy-MM-dd] And when I remove the DATEFORMAT line entirely, I get: Invalid Date Format - length must be 10 or more.

What am I doing wrong? The column in the table is expecting a date in the format of yyyy-MM-dd, but my boss assured me that the time shouldn't affect that
enter image description here

• amazon-web-services
• amazon-s3
• amazon-redshift
• data-integration
• data-engineering

Share Improve this question Follow edited Nov 19, 2024 at 11:16 John Rotenstein 271k2828 gold badges448448 silver badges532532 bronze badges asked Nov 18, 2024 at 23:25 plakosizzleplakosizzle 122 bronze badges 3

• Did you try dateformat ‘auto’? Copy it in as a varchar, what exactly does it display? Never open csv in excel, it always mangles it – user433342 Commented Nov 19, 2024 at 2:05
• Can you provide table DDL and the line form stl_load_errors? It could be many things. Is the column a date or a timestamp? Is it quoted? Is it failing on the first line? Is there a header row? etc. – Bill Weiner Commented Nov 19, 2024 at 2:36
• Got it, thanks guys. The problem was the header line, once I added IGNOREHEADER 1, I was fine – plakosizzle Commented Nov 20, 2024 at 14:52

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I got the answer. My problem was that it was reading the header line as data. Once I added the line IGNOREHEADER 1, it worked. Somewhat misleading error.