Converting Timestamp to Date in Snowflake-CodePudding

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?

CodePudding user response：

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.