Unable to convert string to Timestamp

I’m trying to convert a sample 12 hour date with AM/PM into a timestamp, however, Snowflake is throwing an error that it’s not parsable:

SELECT TO_TIMESTAMP('7/16/2021 4:52:25 AM', 'MM/dd/yyyy HH12:mm:ss AM')

The error message returned:

Can’t parse ‘7/16/2021 4:52:25 AM’ as timestamp with format ‘MM/dd/yyyy HH12:mm:ss AM’

I’ve tried the hours as both HH and HH12 to no avail.

Answer

If you want to extract only the time part you can use the below statement:

SELECT TO_TIME(TO_TIMESTAMP('7/16/2021 4:52:25 AM', 'MM/dd/yyyy HH12:mi:ss AM'))

If you want to convert it to a timestamp format, you can use the below statement:

SELECT TO_TIMESTAMP('7/16/2021 4:52:25 AM', 'MM/dd/yyyy HH12:mi:ss AM')

PFB the documentation links for date extract: https://docs.snowflake.com/en/sql-reference/functions/date_part.html

https://docs.snowflake.com/en/sql-reference/functions/date_trunc.html