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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

So I assume you know how to get the string of the "date" out of the JSON, thus it's really a parsing question.

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)

SELECT
    '2022-02-09T20:28:59 0000' 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: