CODEWITHSUNDEEP
snowflake-cloud-data-platform
MoneyBall
MoneyBall

Reputation: 2563

timestamp "2022-01-02T12:30:30.471395746-06" is not recognized snowflake

I have a timestamp that looks like this:

2022-01-02T12:30:30.471395746-06

I tried running the following code:

alter session set timestamp_input_format = 'AUTO';
select s.$1, s.$2, to_timestamp_tz(s.$3), s.$4, s.$5
     from @my_stage s limit 10;

This gives me an error saying:

Timestamp '2022-01-02T12:30:30.471395746-06' is not recognized

I also tried setting the format to

alter session set timestamp_input_format = 'YYYY-MM-DD"T"HH24:MI:SS.FF9';
alter session set timestamp_output_format = 'YYYY-MM-DD"T"HH24:MI:SS.FF9';

which did not work. Any suggestion would be appreicated!

Upvotes: 1

Views: 450

Answers (1)

Alexander Klimenko
Alexander Klimenko

Reputation: 1685

You are missing the timezone part at the end, it should be specified in the timestamp format as TZH in your case.

The following should work:

alter session set timestamp_input_format = 'YYYY-MM-DDTHH24:MI:SS.FF9TZH';

or

select to_timestamp_tz('2022-01-02T12:30:30.471395746-06', 'YYYY-MM-DDTHH24:MI:SS.FF9TZH');

Output:

2022-01-02T12:30:30.471395746-06:00

Upvotes: 1

Related Questions