Convert YYYYMMDD to MM/DD/YYYY in Snowflake

Question

I need help in figuring out the date conversion logic in Snowflake. The documentation isn't clear enough on this.

In SQL Server, I would try

SELECT CONVERT(DATE, '20200730', 101)

and it gives me '07/30/2020'.

If I try the following in Snowflake,

to_varchar('20200730'::date, 'mm/dd/yyyy')

it gives me '08/22/1970'. Why would it give an entire different date? Need help in getting the logic with the correct date.

score 3 · Accepted Answer · answered Sep 23 '20 at 20:23

The issue with what you are doing is that you are assuming that Snowflake is converting your string of '20200730'::DATE to 2020-07-03. It's not. You need to specify your input format of a date. So, 2 options based on your question being a bit vague:

If you have a string in a table and you wish to transform that into a date and then present it back as a formatted string:

SELECT TO_VARCHAR(TO_DATE('20200730','YYYYMMDD'),'MM/DD/YYYY');
--07/30/2020

If the field in the table is already a date, then you just need to apply the TO_VARCHAR() piece directly against that field.

Unlike SQL Server, Snowflake stores date fields in the same format regardless of what you provide it. You need to use the TO_VARCHAR in order to format that date in a different way...or ALTER SESSION SET DATE_OUTPUT_FORMAT will also work.

Thanks! Marked this as an answer because of detailed explanation what the code is doing. — Julaayi, Sep 23 '20 at 23:43

score 1 · Answer 2 · answered Sep 23 '20 at 18:32

1

Try select to_varchar(TO_DATE( '20200730', 'YYYYMMDD' ), 'MM/DD/YYYY'); which produces 2020-07-30

You may need to refer to https://docs.snowflake.com/en/user-guide/date-time-input-output.html#timestamp-formats

answered Sep 23 '20 at 18:32

Nat Taylor

1,122
7
9

Convert YYYYMMDD to MM/DD/YYYY in Snowflake

2 Answers2