Snowflake query returning false date with conversion

Question

I've a query as below that's converting dates like (20190520,20050511,....) into 19700823.

SELECT NVL(TO_VARCHAR(ALIS.CYCAL_DATE::DATE,'YYYYMMDD'),'00000000')
FROM SCHEMA.VIEW.VIEWNAME_INDE ALIS

Regardless of the input dates, I'm getting output starting with 197008. I've seen last two digits differ for some dates

I'm aware of TO_VARCHAR(TO_DATE(ALIS.CYCAL_DATE,'YYYYMMDD'),'DDMMYYYY'). I just want to know the cause of this faulty conversion. If you've seen this or know the cause, please share.

Answer 1

This isn't a faulty conversion, but rather a misunderstanding of how Snowflake interprets a ::date casting that doesn't specify a date format. If you were to execute that on it's own, you'd see that it's actually treating the value as an integer and adding it to the default datetime in seconds.

select '20190520'::date,
       dateadd(s,20190520,'1970-01-01'::date);

The strings must be in a format that Snowflake expects for a date, like 2019-05-20 in order to use the ::date notation. Otherwise, you should be using the TO_DATE or TRY_TO_DATE functions and specifying the input format of the string.