Convert string of YYYYMMDD to YYYY-MM-DD Date format in Snowflake

user_4802288 0
Viewed 15302

Based on the example mentioned here in the Snowflake documentation, why are the date and timestamp values returning different values just by changing the ORDER BY clause? Also, I am trying to convert a string to a date format which is not returning correct results in Snowflake while this works fine in other SQL based Engines. Need help from experts on this.

This query

SELECT '20200710', TO_DATE('20200710');

is returning the following output

20200710 | 1970-08-22

Also tried:

SELECT TO_DATE('20200710', 'YYYY-MM-DD');

and got the error:

Can't parse '20200710' as date with format 'YYYY-MM-DD'

snowflake-cloud-data-platform sql
1 Answers

To convert to a date data type, you would use:

SELECT TO_DATE('20200710', 'YYYYMMDD')

I would recommend just keeping the date data type. But if you want a string in the format YYYY-MM-DD:

SELECT TO_CHAR(TO_DATE('20200710', 'YYYYMMDD'), 'YYYY-MM-DD')
user_11440350
Related