SSC-FDM-RS0004

It is possible that the date is wrong and Snowflake does not accept wrong dates

Description

In Snowflake, using TO_DATE with an invalid date string (like '20010631') results in an error because it enforces strict validation, rejecting any non-existent dates. In contrast, Redshift's TO_DATE can adjust such invalid dates to the nearest valid date (e.g., rolling June 31 to July 1) if the is_strict parameter is set to false. This difference highlights how Snowflake prioritizes data integrity by not automatically correcting invalid dates, while Redshift allows for more flexibility in date handling.

Code Example

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT TO_DATE('20010631', 'YYYYMMDD', FALSE);

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT TRY_TO_DATE('20010631', 'YYYYMMDD') /*** SSC-FDM-RS0004 - INVALID DATES WILL CAUSE ERRORS IN SNOWFLAKE ***/;

Recommendations

• Check that the date is valid in the TRY_TO_DATE().

• If you need more support, you can email us at [e-mail]