2

I am working within Snowflake and have a column with a timestamp format like this:

timestamp


2021-12-13T21:52:58.656216349+0000
2021-12-13T18:22:01.194783523+0000
2021-12-13T21:03:55.224874997+0000
2021-12-13T21:02:37.075422427+0000
2021-12-13T15:54:26.268433672+0000

my desired output is in a format with:

2021-12-13

Searching from past questions, I found this answer and attempted to modify it for my use by:

SELECT
    timestamp AS original_ts,
    to_timestamp(REPLACE(REPLACE(timestamp,'T',' '),'+',''),'YYYY-MM-DD') AS modified_ts 
FROM
    table

but get the following error:

Can't parse '2022-01-26 00:06:11.1851022090000' as timestamp with format 'YYYY-MM-DD'

How can I resolve this error and format the timestamp column into a more familiar date column ignoring the time entirely?

JLuu
  • 379
  • 1
  • 3
  • 17

2 Answers2

1

I think you want to use TO_DATE here along with LEFT:

SELECT TO_DATE(LEFT(timestamp, 10), 'YYYY-MM-DD') AS modified_ts
FROM yourTable;

Note that if you don't require a bona fide date, but rather just a date string, then LEFT(timestamp, 10) alone should suffice.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

If you use the DATE_TRUNC function then you don’t need to worry about the format of the source column, and therefore you don’t need the LEFT function - assuming it is a date or timestamp column

NickW
  • 8,430
  • 2
  • 6
  • 19