Categories:

Conversion Functions , Date & Time Functions

TO_DATE

Converts an input expression to a date:

  • For a string expression, the result of converting the string to a date.

  • For a timestamp expression, the date from the timestamp.

  • For a variant expression:

    • If the variant contains a string, a string conversion is performed.

    • If the variant contains a date, the date value is preserved as is.

    • If the variant contains a JSON null value, the output is NULL.

  • For NULL input, the output is NULL.

For all other values, a conversion error is generated.

See also:

TRY_TO_DATE

Syntax

TO_DATE( <string_expr> [, <format> ] )
TO_DATE( <timestamp_expr> )
TO_DATE( <variant_expr> )

Arguments

Required:

string_expr or timestamp_expr or variant_expr

Expression to be converted into a date.

Optional:

format

Date format specifier for string_expr or AUTO, which specifies for Snowflake to interpret the format. For more information, see Date and Time Formats in Conversion Functions.

The default is the current value of the DATE_INPUT_FORMAT session parameter (usually AUTO).

Examples

The following examples show how to use the TO_DATE() function.

SELECT TO_DATE('2013-05-17');
+-----------------------+
| TO_DATE('2013-05-17') |
|-----------------------|
| 2013-05-17            |
+-----------------------+
SELECT TO_DATE('2012.07.23', 'YYYY.MM.DD');
+-------------------------------------+
| TO_DATE('2012.07.23', 'YYYY.MM.DD') |
|-------------------------------------|
| 2012-07-23                          |
+-------------------------------------+
SELECT TO_DATE('1999-01-02', 'AUTO');
+-------------------------------+
| TO_DATE('1999-01-02', 'AUTO') |
|-------------------------------|
| 1999-01-02                    |
+-------------------------------+
SELECT TO_DATE('02/14/2014', 'MM/DD/YYYY');
+-------------------------------------+
| TO_DATE('02/14/2014', 'MM/DD/YYYY') |
|-------------------------------------|
| 2014-02-14                          |
+-------------------------------------+