Categories:

# TRY_TO_DATE¶

A special version of TO_DATE , DATE that performs the same operation (i.e. converts an input expression to a date), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

TO_DATE , DATE

## Syntax¶

TRY_TO_DATE( <string_expr> [, <format> ] )
TRY_TO_DATE( '<integer>' )


## Arguments¶

Required:

One of:

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.

The use of quoted integers as inputs is deprecated.

Optional:

format

Date format specifier for string_expr or AUTO, which specifies that Snowflake should automatically detect the format to use. 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).

## Returns¶

The data type of the returned value is DATE.

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

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 is intended to be used as nanoseconds.

This behavior might change in the future.

Note

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, TO_TIME, or TO_TIMESTAMP with strings that contain integers only when those integers are intended to be interpreted as seconds.

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

For example, if the first value is greater than or equal to 31536000000 and less than 31536000000000, then all values are treated as milliseconds.

To process rows in parallel, a set of rows can be divided into groups either by the user (e.g. with a PARTITION BY clause) or by Snowflake. The first row in each group determines whether the value is interpreted as seconds, milliseconds, microseconds, or nanoseconds for the entire group. This can produce different results when the order of the values changes. To avoid this problem, specify the input format for the value, either as an argument to the function or in a session parameter.

## Examples¶

This demonstrates usage of TRY_TO_DATE:

SELECT TRY_TO_DATE('2018-09-15'), TRY_TO_DATE('Invalid');
+---------------------------+------------------------+
| TRY_TO_DATE('2018-09-15') | TRY_TO_DATE('INVALID') |
|---------------------------+------------------------|
| 2018-09-15                | NULL                   |
+---------------------------+------------------------+