Categories:

Conversion Functions , Date & Time Functions

TO_DATE , 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( '<integer>' )
TO_DATE( <variant_expr> )

DATE( <string_expr> [, <format> ] )
DATE( <timestamp_expr> )
DATE( '<integer>' )
DATE( <variant_expr> )

Arguments

Required:

One of:

timestamp_expr

A TIMESTAMP expression. The DATE portion of the TIMESTAMP is extracted.

string_expr

String from which to extract a date, for example ‘2019-01-31’.

integer

An expression that evaluates to a string containing an integer, for example ‘15000000’. Depending upon the magnitude of the string, it can be interpreted as seconds, milliseconds, microseconds, or nanoseconds. For details, see the Usage Notes below.

variant_expr

An expression of type VARIANT. The VARIANT must contain a DATE, or a string that contains an INTEGER. (TO_DATE returns an error if passed a VARIANT containing a timestamp.)

The VARIANT must contain one of the following:

  • A string from which to extract a date.

  • A date.

  • A string containing an integer number of seconds or milliseconds.

Although TO_DATE accepts a TIMESTAMP value, it does not accept a TIMESTAMP inside a VARIANT.

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).

Usage Notes

  • If the format of the input parameter is a string that contains an integer:

    • After the string is converted to an integer, the integer is treated as a number of seconds, milliseconds, microseconds, or nanoseconds after the start of the Unix epoch (1970-01-01 00:00:00.000000000 UTC).

      • If the integer is less than 31536000000 (the number of milliseconds in a year), then the value is treated as a number of seconds.

      • If the value is greater than or equal to 31536000000 and less than 31536000000000, then the value is treated as milliseconds.

      • If the value is greater than or equal to 31536000000000 and less than 31536000000000000, then the value is treated as microseconds.

      • If the value is greater than or equal to 31536000000000000, then the value is treated as nanoseconds.

    • If more than one row is evaluated (for example, if the input is the column name of a table that contains more than two rows), the first processed value determines whether all subsequent values are treated as seconds, milliseconds, microseconds, or nanoseconds.

      If the first value is greater than or equal to 31536000000, then all values will be treated as milliseconds, even if some remaining values are less than 31536000000. Similar logic applies for microseconds and nanoseconds.

    Caution

    Currently, negative values are always treated as seconds. For example, -31536000000000000000 is treated as a number of seconds before the year 1970, although its scale implies that it’s intended to be used as nanoseconds.

    This behavior might change in the future.

    Caution

    Deprecation Warning: Future versions of Snowflake might automatically interpret stringified integer values as seconds, rather than as milliseconds, microseconds, or nanoseconds. Snowflake recommends that you call TO_DATE or TO_TIMESTAMP with strings that contain integers only when those integers are intended to be interpreted as seconds.

Examples

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

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

This example shows how the first value converted can control whether the subsequent values are interpreted as seconds or milliseconds:

Create and load the table:

CREATE TABLE demo1 (
    description VARCHAR,
    value VARCHAR -- yes, string rather than bigint
    );

Contrast the output of the following two queries. The only difference between the two is the order of the rows:

SELECT 
       description, 
       value, 
       TO_TIMESTAMP(value), 
       TO_DATE(value)
    FROM demo1
    ORDER BY value ASC
    ;
+--------------+-------------+-------------------------+----------------+
| DESCRIPTION  | VALUE       | TO_TIMESTAMP(VALUE)     | TO_DATE(VALUE) |
|--------------+-------------+-------------------------+----------------|
| Seconds      | 31535999999 | 2969-05-02 23:59:59.000 | 2969-05-02     |
| Milliseconds | 31536000000 | 2969-05-03 00:00:00.000 | 2969-05-03     |
+--------------+-------------+-------------------------+----------------+
SELECT 
       description, 
       value, 
       TO_TIMESTAMP(value), 
       TO_DATE(value)
    FROM demo1
    ORDER BY value DESC
    ;
+--------------+-------------+-------------------------+----------------+
| DESCRIPTION  | VALUE       | TO_TIMESTAMP(VALUE)     | TO_DATE(VALUE) |
|--------------+-------------+-------------------------+----------------|
| Milliseconds | 31536000000 | 1971-01-01 00:00:00.000 | 1971-01-01     |
| Seconds      | 31535999999 | 1970-12-31 23:59:59.999 | 1970-12-31     |
+--------------+-------------+-------------------------+----------------+