Snowflake - Date column has character, can't convert to datetime-CodePudding

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

CodePudding user response：

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:

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

CodePudding user response：

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