Snowflake Timestamp column not loaded using Copy Command

Question

I am using Copy command to load a file in table. It has a timestamp column.

In my File format I have defined Timestamp as other and gave value as MM/DD/YYYY HH:MI:SS AM to match with data.

When I execute it loads all records which have timestamp with AM format and any records with timestamp of PM time fails in Copy.

Sample fail record: 1, abc, 04/12/2016 12:00:00 PM

Sample successfully Loaded Record: 2, erd, 04/12/2016 08:00:00 AM

To verify my timestamp format I used below query and it worked fine: SELECT TO_TIMESTAMP('04/12/2016 12:00:00 PM','MM/DD/YYYY HH:MI:SS AM')

Answer 1

The AM in your format definition is for HH12 not HH24 and is otherwise just considered a literal string. Try explicitly using HH12 in your format definition and see if that resolves your issue. I agree that it should behave the same as TO_TIMESTAMP() but since it isn't, I'd try to follow the guidelines of the documentation in your file format.

https://docs.snowflake.com/en/sql-reference/functions-conversion.html#date-and-time-formats-in-conversion-functions