how to convert long verbal datetime to timestamp (YY-MM-DD HH:MM:SS) in snowflake?

Question

I'm using snowflake dates. I have date in weird pattern (output from database):

Wed Apr 21 2021 22:11:32 GMT+0300 (Israel Daylight Time)

I need to parse it as datetime- YY-MM-DD HH:MM:SS. if I try this out:

SELECT TO_TIMESTAMP_NTZ('Wed Apr 21 2021 22:11:32 GMT+0300 (Israel Daylight Time)', 'YY:MM:DD 
HH:MM:SS')

I get this error:

SQL Error [100096] [22007]: Can't parse 'Wed Apr 21 2021 22:11:32 GMT+0300 (Israel Daylight Time)' as 
timestamp with format 'YY:MM:DD HH:MM:SS'

and so on in every function I tried!! (TO_TIMESTAMP_NTZ, TO_TIMESTAMP_LTZ, TO_TIMESTAMP_TZ, TO_TIMESTAMP, TO_DATETIME, TO_DATE, TO_TIME). any idea?

Answer 1

Using the values at Timestamp Formats, and trimming the string down we can get the following working

SELECT TO_TIMESTAMP_NTZ('Wed Apr 21 2021 22:11:32', 'DY MON DD YYYY HH:MM:SS');

adding the timezone back in with

SELECT TO_TIMESTAMP_NTZ('Wed Apr 21 2021 22:11:32 GMT+0300', 'DY MON DD YYYY HH:MM:SS GMTTZHTZM');

this works, but gives a NoTimeZone value, when the value has a timezone, so purhaps NTZ is not what you wanted.

But the (Israel Daylight Time) part is throwing us for a loop, so lets get rid of that with a REGEX_SUBSTR

SELECT 'Wed Apr 21 2021 22:11:32 GMT+0300 (Israel Daylight Time)' as in_str
    ,REGEXP_SUBSTR( in_str , '(.*) \\(',1,1,'c',1) as regex_str 
    ,TO_TIMESTAMP_NTZ(regex_str, 'DY MON DD YYYY HH:MM:SS GMTTZHTZM') as time
    ;

gives:

  IN_STR
  Wed Apr 21 2021 22:11:32 GMT+0300 (Israel Daylight Time)

  REGEX_STR
  Wed Apr 21 2021 22:11:32 GMT+0300 

  TIME
  2021-11-21 22:00:32.000