How To Convert Long Verbal Datetime To Timestamp (yy-mm-dd Hh:mm:ss) In Snowflake?

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 H

Solution 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_STRWedApr212021 22:11:32 GMT+0300(IsraelDaylightTime)REGEX_STRWedApr212021 22:11:32 GMT+0300TIME2021-11-21 22:00:32.000