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

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'

3

1 Answer

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') 
1

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobWxpZGmDeX%2BOnKanrpWnwW6%2F06ugp59dpLNuxdiysKallJl6tbuMsrCysV2ium6ww2abmqyVYrOwvsyaq2ahnmLAr7vWn6Oao5U%3D