correct type for SQL snowflake date

Question

I am using an SQL Script to parse a json into a table using dbt. One of the cols had this date value: '2022-02-09T20:28:59+0000'. What would be the correct way to define iso date's data type in Snowflake?

Currently, I just used the date type like this in my dbt sql script:

JSON_DATA:"situation_date"::date AS MY_DATE

but clearly, dateisn't the correct one because later when I test it using select * , I get this error:

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

so I need to know which Snowflake date data type or datetime type suits the best with this one

Answer 1

Correct pulling the "date from JSON" so not so clear cut:

SELECT
    '{"date":"2022-02-09T20:28:59+0000"}' as json_str
    ,parse_json(json_str) as json
    ,json:date as data_from_json
    ,TRY_TO_TIMESTAMP_NTZ(data_from_json, 'YYYY-MM-DDTHH:MI:SS+0000') as date_1
    ,TRY_TO_TIMESTAMP_NTZ(substr(data_from_json,1,19), 'YYYY-MM-DDTHH:MI:SS') as date_2
    ;

gives the error:

Function TRY_CAST cannot be used with arguments of types VARIANT and TIMESTAMP_NTZ(9)

Because the type of data_from_json as VARIANT and the TO_DATE/TO_TIMESTAMP function expect TEXT so we need to cast to that

SELECT
    '{"date":"2022-02-09T20:28:59+0000"}' as json_str
    ,parse_json(json_str) as json
    ,json:date as data_from_json
    ,TRY_TO_TIMESTAMP_NTZ(data_from_json::text, 'YYYY-MM-DDTHH:MI:SS+0000') as date_1
    ,TRY_TO_TIMESTAMP_NTZ(substr(data_from_json::text,1,19), 'YYYY-MM-DDTHH:MI:SS') as date_2
    ;

If all your timezones are always +0000 you can just put that in the parse format (like example date_1), OR you can truncate that part off (like example date_2)

gives:

JSON_STR	JSON	DATA_FROM_JSON	DATE_1	DATE_2
{"date":"2022-02-09T20:28:59+0000"}	{ "date": "2022-02-09T20:28:59+0000" }	"2022-02-09T20:28:59+0000"	2022-02-09 20:28:59.000	2022-02-09 20:28:59.000

Answer 2

Using TRY_TO_TIMESTAMP:

SELECT TRY_TO_TIMESTAMP(JSON_DATA:"situation_date", 'format_here')
FROM tab;

---

so I need to know which Snowflake date data type or datetime type suits the best with this one

TIMESTAMP_INPUT_FORMAT

The specific input could be set up on ACCOUNT/USER/SESSION level.

AUTO Detection of Integer-stored Date, Time, and Timestamp Values

Avoid using AUTO format if there is any chance for ambiguous results. Instead, specify an explicit format string by:

Setting TIMESTAMP_INPUT_FORMAT and other session parameters for dates, timestamps, and times. See Session Parameters for Dates, Times, and Timestamps (in this topic).

Answer 3

I think ::TIMESTAMP should work for this. So JSON_DATA:"situation_date"::TIMESTAMP if you need to go just to date after, you could then to ::Date or to_Date()

Answer 4

After some testing, it seems to me you have 2 options.

Either you can get rid of the +0000 at the end:
left(column_date, len(column_date)-5)::timestamp

or use the function 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 both are TimeZone Offset Hours and Minutes