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  
TRY_TO_BINARY Error-handling version to TO_BINARY.
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  
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  

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.


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


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.