SSC-FDM-RS0004

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

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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(/*** SSC-FDM-RS0004 - INVALID DATES WILL CAUSE ERRORS IN SNOWFLAKE ***/ '20010631', 'YYYYMMDD');

Recommendations

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

• If you need more support, you can email us at [email protected]