Errors converting to date-time in snowflake with the T delimiter

Question

I am new to snowflake and I am trying to convert to timestamp with the T delimiter. I have looked at examples but I seem to be getting errors. I am not sure what I am getting wrong

My code :

select Id,
     to_timestamp_ntz(dt."Value",'YYYY-MM-DD"T"HH24:MI:SS'),
    from Accounts

Errors

Can't parse '2018-09-08 07:40:45' as timestamp with format 'YYYY-MM-DD"T"HH24:MI:SS'

Answer 1

For the timestamp format you want the input timestamp should look like '2018-09-08T07:40:45', yours is missing the T.

So this works:

select to_timestamp_ntz('2018-09-08T07:40:45','YYYY-MM-DD"T"HH24:MI:SS');

I get:

2018-09-08 07:40:45.000

or otherwise use for that input timestamp the format:

select to_timestamp_ntz('2018-09-08 07:40:45','YYYY-MM-DD HH24:MI:SS');

which gives same output.

Answer 2

You command is doing an input formatting which means that you're saying that 2018-09-08 07:40:45 is in format YYYY-MM-DD"T"HH24:MI:SS which is not true because you are missing the T between the date and time in your input.

If you'd like to do an output formatting you would use TO_CHAR instead.

E.g.

select to_char(to_timestamp('2018-09-08 07:40:45'), 'YYYY-MM-DD"T"HH24:MI:SS');

Returns:

2018-09-08T07:40:45