DateTime in Snowflake Timestamp '23-Jan-2015 23:02:39' is not recognized

user_9530864 0
Viewed 3054

So I have a varchar that I am converting to a DateTime and I'm using the below syntax but I get the following error in Snowflake "Timestamp '23-Jan-2015 23:02:39' is not recognized". Since I'll be getting my data in this format what do you suggest?

select '23-Jan-2015 23:02:39'::timestamp_tz;

snowflake-cloud-data-platform sql
2 Answers

Your timestamp string literal is not in a standard format which Postgres can directly use. Instead, you may use TO_TIMESTAMP to convert:

SELECT TO_TIMESTAMP('23-Jan-2015 23:02:39', 'DD-Mon-YYYY HH24:MI:SS');

In the demo tool I used, this returned: 2015-01-23 23:02:39+00

Demo

user_18632290

You need to let snowflake know how to make sense of the literal that you are passing.

  1. Use the to_timestamp() to instruct it to convert to a timestamp
  2. Give it a way to map the day-month-year while it parses the literal to map it to values

select to_timestamp('23-Jan-2015 23:02:39', 'DD-MON-YYYY HH24:MI:SS');

Please check the supported Date ,Time , Timestamp etc formats supported by Snowflake here: https://docs.snowflake.com/en/user-guide/date-time-input-output.html#date-formats

user_37374780
Related