Snowflake - Date string has T character, can't convert to datetime

user_14444816 0
Viewed 83

I have several columns that look like this with a T and +: 2020-04-11T21:00:09+0000

I want to convert them to datetime if possible, I've tried to_timestamp_ntz() and to_date():

to_timestamp_ntz('2020-04-11T21:00:09+0000', 'YYYY-MM-DD HH24:MI:SS.FF+00')

but I keep seeing:

Can't parse '2020-04-11T21:00:09+0000' as timestamp with format...

snowflake-cloud-data-platform sql
2 Answers

It is a matter of format:

SELECT to_timestamp_ntz('2020-04-11T21:00:09+0000', 
                        'YYYY-MM-DD"T"HH24:MI:SSTZHTZM') AS res

To handle T it needs to be provided as "T".

Output:

enter image description here

The pattern "..." inside format works for arbitrary text:

SELECT to_timestamp_ntz('2020-04-11aaaa21:00:09+0000', 
                        'YYYY-MM-DD"aaaa"HH24:MI:SSTZHTZM') AS res
-- 2020-04-11 21:00:09.000
user_50708790

I don't think there's a way to specify a date/time format to skip over a character like that. You may have to do something like this:

select to_timestamp_ntz(replace('2020-04-11T21:00:09+0000', 'T', ' '), 'YYYY-MM-DD HH:MI:SSTZHTZM')
user_127563810
Related