Snowflake GMT String to Date Conversion

Question

We receive a string '2019-11-30T18:00:00GMT-06:00' in the JSON file and this need to be converted to timestamp to load into the timestamp column in the snowflake. I tried multiple options convert_timezone,to_timestamp etc, however in vain, Can you please let me know how i represent this string (2019-11-30T18:00:00GMT-06:00) in data format for comversion.

Thanks !

Answer 1

Leveraging a more Snowflake way to do this, you'd want to run something like this:

SELECT TO_TIMESTAMP('2019-11-30T18:00:00GMT-06:00','YYYY-MM-DDTHH:MI:SSGMT-TZH:TZM');

The output of this will be the date/time of your account default along with the timezone offset that goes along with that.

Answer 2

Please try the below mentioned approach

if your table is create as below

CREATE TABLE TIMESTAMP_TEST(DATE TIMESTAMP);

Insert the value as

INSERT INTO TIMESTAMP_TEST SELECT REPLACE(REPLACE('2019-11-30T18:00:00GMT-06:00','GMT'),'T',' ') FROM DUAL

Thanks