Conversion Functions

This family of functions can be used to convert an expression of any Snowflake data type to another data type.

In this Topic:

List of Functions

Sub-category

Function

Notes

Any Data Type

CAST , ::

TRY_CAST

Error-handling version of CAST.

Text/Character/Binary Data Types

TO_CHAR , TO_VARCHAR

TO_BINARY

TRY_TO_BINARY

Error-handling version to TO_BINARY.

Numeric Data Types

TO_DECIMAL , TO_NUMBER , TO_NUMERIC

TO_DOUBLE

TRY_TO_DECIMAL, TRY_TO_NUMBER, TRY_TO_NUMERIC

Error-handling versions of TO_DECIMAL, TO_NUMBER, etc.

TRY_TO_DOUBLE

Error-handling version of TO_DOUBLE.

Boolean Data Type

TO_BOOLEAN

TRY_TO_BOOLEAN

Error-handling version of TO_BOOLEAN.

Date and Time Data Types

TO_DATE

TO_TIME

TO_TIMESTAMP / TO_TIMESTAMP_*

TRY_TO_DATE

Error-handling version of TO_DATE.

TRY_TO_TIME

Error-handling version of TO_TIME.

TRY_TO_TIMESTAMP / TRY_TO_TIMESTAMP_*

Error-handling versions of TO_TIMESTAMP, etc.

Semi-structured Data Types

TO_ARRAY

TO_OBJECT

TO_VARIANT

Error-handling Conversion Functions

Conversion functions with a TRY_ prefix are special versions of their respective conversion functions. These functions return a NULL value instead of raising an error when the conversion can not be performed:

Note that these functions only support string expressions (i.e. VARCHAR or CHAR data type) as input.

In addition, TRY_TO_DATE, TRY_TO_TIME, and TRY_TO_TIMESTAMP/TRY_TO_TIMESTAMP_* do not support the additional format parameter supported by their regular conversion counterparts.

Important

These error-handling conversion functions are optimized for situations where conversion errors are relatively infrequent:

  • If there are no (or very few) errors, they should result in no visible performance impact.

  • If there are a large number of conversion failures, using these functions can result in significantly slower performance. Also, when using them with the VARIANT type, some operations might result in reduced performance.

Numeric Formats in Conversion Functions

The functions TO_DECIMAL , TO_NUMBER , TO_NUMERIC, and TO_DOUBLE accept an optional parameter that specifies the format of the input string, if the input expression evaluates to a string. For more information about the values this parameter can have, see SQL Format Models.

Date and Time Formats in Conversion Functions

TO_CHAR , TO_VARCHAR, TO_DATE, TO_TIME, and all the TO_TIMESTAMP / TO_TIMESTAMP_* variations accept an optional argument specifying the expected format to parse or produce a string. For dates, times, and timestamps, the format can consist of any text, including the following key symbols (case-insensitive):

Format

Description

YYYY

Four-digit year.

YY

Two-digit year, controlled by the TWO_DIGIT_CENTURY_START session parameter, e.g. when set to 1980, values of 79 and 80 parsed as 2079 and 1980 respectively.

MM

Two-digit month (01=January, etc.).

MON

Full or abbreviated month name.

MMMM

Full month name.

DD

Two-digit day of month (01 through 31).

DY

Abbreviated day of week.

HH24

Two digits for hour (00 through 23); am/pm NOT allowed.

HH12

Two digits for hour (01 through 12); am/pm allowed.

AM , PM

Ante meridiem (am) / post meridiem (pm); for use with HH12.

MI

Two digits for minute (00 through 59).

SS

Two digits for second (00 through 59).

FF

Fractional seconds with precision 0 (seconds) to 9 (nanoseconds), e.g. FF, FF0, FF3, FF9. Specifying FF is equivalent to FF6 (microseconds).

TZH:TZM , TZHTZM , TZH

Time zone hour and minute, offset from UTC. Can be prefixed by +/- for sign.

Usage Notes

Anything in the format between double quotes or other than the above elements is parsed/formatted without being interpreted.

Examples

SELECT TO_DATE('3/4/2013', 'dd/mm/yyyy');

-----------------------------------+
 to_date('3/4/2013', 'dd/mm/yyyy') |
-----------------------------------+
 2013-04-03                        |
-----------------------------------+
SELECT TO_VARCHAR('2013-04-05'::date, 'mon dd, yyyy');

------------------------------------------------+
 to_varchar('2013-04-05'::date, 'mon dd, yyyy') |
------------------------------------------------+
 Apr 05, 2013                                   |
------------------------------------------------+

Binary Formats in Conversion Functions

TO_CHAR , TO_VARCHAR and TO_BINARY accept an optional argument specifying the expected format to parse or produce a string.

The format can be one of the following strings (case-insensitive):

  • HEX

  • BASE64

  • UTF-8

For more information about these formats, see Overview of Supported Binary Formats.

For examples of using these formats, see the Examples section of Binary Input and Output.