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 may result in significantly slower performance. Also, when using them with the VARIANT type, some operations may result in reduced performance.

Date, Time, and Binary Formats in Conversion Functions

TO_CHAR , TO_VARCHAR, TO_BINARY, 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 (can be prefixed by +/- for sign; Z only means UTC).

For binary, the format can be one of the following strings (case-insensitive):

  • HEX
  • BASE64
  • UTF-8

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