TO_DATE

Date function

Description

TO_DATE converts a date represented by a character string to a DATE data type. (Redshift SQL Language Reference TO_DATE function)

This function is fully supported in Snowflake.

For more information about quoted identifiers in functions, click here.

Grammar Syntax

TO_DATE(string, format, [is_strict])

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT TO_DATE('02 Oct 2001', 'DD Mon YYYY');
SELECT TO_DATE('20010631', 'YYYYMMDD', FALSE);
SELECT TO_DATE('20010631', 'YYYYMMDD', TRUE);
SELECT TO_DATE('1,993 12 23', 'Y,YYY MM DD');
SELECT TO_DATE(d, 'YYYY/MM/DD'),
       TO_DATE(d, f)
       FROM (SELECT '2001-01-01'::date as d, 'DD/MM/YYYY' as f);

Results

to_date	to_date	to_date
2001-10-02	2001-07-01	[22008] ERROR: date/time field date value out of range: 2001-6-31

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT TO_DATE('02 Oct 2001', 'DD Mon YYYY');
SELECT
       TRY_TO_DATE(/*** SSC-FDM-RS0004 - INVALID DATES WILL CAUSE ERRORS IN SNOWFLAKE ***/ '20010631', 'YYYYMMDD');
SELECT TO_DATE('20010631', 'YYYYMMDD');
SELECT TO_DATE('1,993 12 23', 'Y,YYY MM DD') !!!RESOLVE EWI!!! /*** SSC-EWI-PG0005 - Y,YYY MM DD FORMAT MAY FAIL OR MAY HAVE A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/!!!;
SELECT TO_DATE(d, 'YYYY/MM/DD'),
       --** SSC-FDM-0032 - PARAMETER 'format_string' IS NOT A LITERAL VALUE, TRANSFORMATION COULD NOT BE FULLY APPLIED **
       TO_DATE(d, f)
       FROM (SELECT '2001-01-01'::date as d, 'DD/MM/YYYY' as f);

Results

to_date	to_date	to_date
2001-10-02	NULL	Can't parse '20010631' as date with format 'YYYYMMDD'

Known Issues

1. The query SELECT TO_DATE('20010631', 'YYYYMMDD') fails in Snowflake because June has only 30 days, and Snowflake's TO_DATE does not adjust invalid dates automatically, unlike Redshift's TO_DATE with is_strict set to false, which would convert it to July 1. To avoid errors with invalid date strings, you can use TRY_TO_DATE, which returns NULL if the conversion fails. This allows for smoother query execution and easier identification of invalid dates.

Related EWIs

• SSC-FDM-RS0004: Invalid dates will cause errors in Snowflake.

• SSC-EWI-PG0005: Date or time format is not supported in Snowflake.

• SSC-FDM-0032: Parameter is not a literal value, transformation could not be fully applied