Search code examples
sqlsnowflake-cloud-data-platformsnowsql

Convert YYYYMMDD to MM/DD/YYYY in Snowflake

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.

Solution

  • 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.