timestamp VS TIMESTAMP_NTZ in snowflake sql

Question

I am using an sql script to parse a json into a snowflake table using dbt.

One of the cols contain this datetime value: '2022-02-09T20:28:59+0000'.

What's the correct way to define ISO datetime's data type in Snowflake?

I tried date, timestamp and TIMESTAMP_NTZ like this in my dbt sql script:

JSON_DATA:",my_date"::TIMESTAMP_NTZ AS MY_DATE

but clearly, these aren't the correct one because later on when I test it in snowflake with select * , I get this error:

SQL Error [100040] [22007]: Date '2022-02-09T20:28:59+0000' is not recognized

or

SQL Error [100035] [22007]: Timestamp '2022-02-13T03:32:55+0100' is not recognized

so I need to know which Snowflake time/date data type suits the best for this one

EDIT: This is what I am trying now.

SELECT 
    JSON_DATA:"date_transmission" AS DATE_TRANSMISSION
  , TO_TIMESTAMP(DATE_TRANSMISSION:text, 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM') AS DATE_TRANSMISSION_TS_UTC
    , JSON_DATA:"authorizerClientId"::text AS AUTHORIZER_CLIENT_ID
  , JSON_DATA:"apiPath"::text API_PATH
    , MASTERCLIENT_ID
  , META_FILENAME
  , META_LOAD_TS_UTC
  , META_FILE_TS_UTC
FROM {{ source('INGEST_DATA', 'TABLENAME') }} 

I get this error:

000939 (22023): SQL compilation error: error line 6 at position 4
10:21:46    too many arguments for function [TO_TIMESTAMP(GET(DATE_TRANSMISSION, 'text'), 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM')] expected 1, g

However, if I comment out the the first 2 lines(related to timpstamp types), the other two work perfectly fine. What's the correct syntax of parsing json with TO_TIMESTAMP?

Not that JSON_DATA:"apiPath"::text API_PATH gives the correct value for it in my snowflake tables.

Answer 1

Did some testing and it seems you have 2 options.

You can either get rid of the +0000 at the end: left(column_date, len(column_date)-5)

or try_to_timestamp with format try_to_timestamp('2022-02-09T20:28:59+0000','YYYY-MM-DD"T"HH24:MI:SS+TZHTZM')

TZH and TZM are TimeZone Offset Hours and Minutes