0

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'),
jedrek
  • 41
  • 1
  • 1
  • 10

3 Answers3

1

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')
Simon D
  • 5,730
  • 2
  • 17
  • 31
  • Hi @simon, when I used your code I get below error _Can't parse '"2021-03-31 20:00:33.205903+00"' as timestamp with format 'YYYY-MM-DD HH24:MI:SS.FF+00'_ – jedrek Apr 01 '21 at 10:44
  • Can you show your exact code? because this works for me: `select to_timestamp_ntz('2021-03-31 20:00:33.205903+00', 'YYYY-MM-DD HH24:MI:SS.FF+00');` – Simon D Apr 01 '21 at 10:49
  • 1
    Just to add that the proposed solution from @SimonDarr works for me too. – Francesco Quaratino Apr 01 '21 at 10:56
  • I download data from Azure Data Lake and create external table. This is just a format of date in csv file _2021-04-01 08:00:11.211201+00_. The code I use in snowflake for this column looks like this _TO_TIMESTAMP_NTZ((value: c4::STRING),'YYYY-MM-DD HH24:MI:SS.FF+00')_ – jedrek Apr 01 '21 at 11:20
  • please share the full SQL and error message returned – Francesco Quaratino Apr 01 '21 at 13:26
0

  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 |
+-------------------------------+----------------------------+-------------------------+
Gokhan Atil
  • 9,278
  • 2
  • 11
  • 24
  • Hi, unfortunatelly I am still getting error _Can't parse '"2021-03-31 17:00:20.975882' as timestamp with format 'YYYY-MM-DD HH24:MI:SS.FF'_ – jedrek Apr 01 '21 at 10:46
0

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.

jedrek
  • 41
  • 1
  • 1
  • 10