Cast VARCHAR to Datetime

Question

I'm trying to convert a string datetime, to a timestamp. try_to_timestamp isn't converting dates in the yyyy-mm-dd hh:mm:ss AM/PM format. I've only been able to solve this by striping out the time and casting it as a type time and concatenating it back with the date. Anyone know of a Snowflake function to handle this?

select try_to_timestamp('2019-11-18 4:01:29 PM +0000')

Answer 1

your timestamp doesn't quite fit the ISO or RTC formats so it's not automatically detecting it. You can manually put in your format though. Should look something like this:

select to_timestamp('2019-11-18 4:01:29 PM +0000', 'YYYY-MM-DD HH12:MI:SS AM TZHTZM')

For information on the various formats SnowFlake uses: https://docs.snowflake.net/manuals/user-guide/date-time-input-output.html

EDIT: You may or may not want to cast it as a timestamp_tz (timestamp with timezone) to maintain timezone information

select to_timestamp_tz('2019-11-18 4:01:29 PM +0200', 'YYYY-MM-DD HH12:MI:SS AM TZHTZM');

Answer 2

Since try_to_timestamp does not support a format, you can set it before calling the try_to_timestamp function. The example below is setting it at the session level.

 alter session set TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH12:MI:SS AM TZHTZM'; 

 select  try_to_timestamp('2019-11-18 4:01:29 PM +0000');