Converting Timestamp to Date in Snowflake

Question

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?

Tim Biegeleisen · Accepted Answer · 2022-07-03 04:18:06Z

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.

answered Jul 3, 2022 at 4:18

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JLuu Over a year ago

That's it! Simpler than I thought! thank you!

NickW · Accepted Answer · 2022-07-03 16:15:09Z

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

answered Jul 3, 2022 at 16:15

NickW

10k2 gold badges10 silver badges26 bronze badges

Collectives™ on Stack Overflow

Converting Timestamp to Date in Snowflake

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related