Error while loading csv file to Snowflake Table as "Timestamp '9/15/2020 1:28:00 AM' is not recognized"

Question

csv_file:

|column_1 | Column_2 | Column_3             |
|===========================================|
|abc      | xyz      | 9/15/2020 1:28:00 AM |

I am trying to load a file from s3 to snowflake using following command:

COPY INTO  table_name 
FROM @stage/file_name.csv  
FILE_FORMAT = (
   type = csv 
   SKIP_HEADER = 1
) 
FORCE = TRUE 
ON_ERROR = Continue;

but while executing this query getting error as Timestamp '9/15/2020 1:28:00 AM' is not recognized. Is there any way so that I will be able to load the data to table without getting any error ?

Answer 1

Snowflake tries to automatically detect the timestamp format of files loaded if you don't explicitly specify it. Sometimes date strings however can match multiple input formats so Snowflake can't guarantee that the date is parsed as you intended.

You can either set the TIMESTAMP_INPUT_FORMAT session parameter before loading to 'MM/DD/YYYY HH12:MI:SS AM' or you can specify it as a file_format option like below (untested):

COPY INTO table_name 
FROM @stage/file_name.csv 
FILE_FORMAT = (
      type = csv 
      SKIP_HEADER = 1 
      TIMESTAMP_FORMAT = 'MM/DD/YYYY HH12:MI:SS AM'
   ) 
FORCE = TRUE ON_ERROR = Continue;