CODEWITHSUNDEEP
sqldatetimetimestampsnowflake-cloud-data-platform
JLuu
JLuu

Reputation: 373

Converting Timestamp to Date in Snowflake

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?

Upvotes: 0

Views: 6586

Answers (2)

NickW
NickW

Reputation: 9768

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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.

Upvotes: 1

Related Questions