CODEWITHSUNDEEP
datetimetimetimestampsnowflake-cloud-data-platformsql-timestamp
jedrek
jedrek

Reputation: 91

Snowflake - convert string to datetime

I am using snowflake and I have date as a string in this format

'2021-04-01 08:00:05.577209+00'

I want to convert it to DateTime. I used the below code to do this (I trim '+00' from each string first). However I think I defined it somehow wrong, so I keep getting errors.

TO_TIMESTAMP_NTZ(left(ts,len(ts)-4),'YYYY-MM-DD HH24:MI:SS.FF'),

Upvotes: 0

Views: 3227

Answers (3)

jedrek
jedrek

Reputation: 91

I have found answer on my question. I was reading data from CSV files on Azure Data Lake and I haven't noticed quotes in a columns. When I deleted them everything is working fine.

Upvotes: 0

Simon D
Simon D

Reputation: 6229

Why do you want to trim the +00 off? just do it like this:

select to_timestamp_ntz('2021-04-01 08:00:05.577209+00', 'YYYY-MM-DD HH24:MI:SS.FF+00')

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10039

  1. It would be better to use left(ts,len( ts)-3) instead of left(ts,len( ts)-4) to trim last 3 characters.

  2. Can you check your data and be sure it is '2021-04-01 08:00:05.577209+00' cause it works as expected (tested with both):

    select ts, left(ts,len( ts)-3) trimmed, TO_TIMESTAMP_NTZ(left(ts,len( ts)-3),'YYYY-MM-DD HH24:MI:SS.FF') result from values ('2021-04-01 08:00:05.577209+00') tmp (ts);

Result:

+-------------------------------+----------------------------+-------------------------+
|              TS               |          TRIMMED           |         RESULT          |
+-------------------------------+----------------------------+-------------------------+
| 2021-04-01 08:00:05.577209+00 | 2021-04-01 08:00:05.577209 | 2021-04-01 08:00:05.577 |
+-------------------------------+----------------------------+-------------------------+

Upvotes: 0

Related Questions