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

Question

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'

Your second example should be SELECT TO_DATE('20200710', 'YYYYMMDD'); - the format is for the incoming string, not the output format. It returns a DATE type which doesn't actually have a "format" like you think. I have no idea why the first example is behaving the way it is - that makes no sense. You mention ORDER BY in part of your question, but don't provide any examples, so not sure what you're on about? — Clockwork-Muse
@Clockwork-Muse Thanks for clarifying about the format. I am referring to the ORDER BY clause in the example of the documentation. — Julaayi
Okay - after finally reading the "Usage Notes" section - the values are changing because the implementers made some extremely questionable design and implementation decisions. Note that their example implies that the database will not be able to evaluate similar queries in parallel, which may result in massive performance penalties. It also means that any query providing a numeric value, but not an ORDER BY, yields undefined results. Given this, I would avoid this product in favor of something else. — Clockwork-Muse

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-07-29T00:48:50

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

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

1 Answers