CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformdbtsnowflake-schemaisodate
x89
x89

Reputation: 3470

correct type for SQL snowflake date

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

Upvotes: 1

Views: 1000

Answers (4)

Brandon Coleman
Brandon Coleman

Reputation: 113

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

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26043

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

Upvotes: 1

Brandon Coleman
Brandon Coleman

Reputation: 113

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()

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175954

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).

Upvotes: 0

Related Questions