Conversion Functions¶

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

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 , DATE

TO_TIME , 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

Geospatial Data Types

TO_GEOGRAPHY

TRY_TO_GEOGRAPHY

Error-handling version of TO_GEOGRAPHY

ST_GEOGFROMGEOHASH

ST_GEOGPOINTFROMGEOHASH

ST_GEOGRAPHYFROMWKB

ST_GEOGRAPHYFROMWKT

TO_GEOMETRY

TRY_TO_GEOMETRY

Error-handling version of TO_GEOMETRY

ST_GEOMETRYFROMWKB

ST_GEOMETRYFROMWKT

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.

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¶

The following functions allow you to specify the expected date, time, or timestamp format to parse or produce a string:

You specify the format in an optional argument, using the following case-insensitive elements to describe the format:

Format Element

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). You must not specify AM / PM.

HH12

Two digits for hour (01 through 12). You can specify AM / PM.

AM , PM

Ante meridiem (am) / post meridiem (pm). Use this only with HH12 (not with HH24).

MI

Two digits for minute (00 through 59).

SS

Two digits for second (00 through 59).

FF[0-9]

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

TZH:TZM , TZHTZM , TZH

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

UUUU

Four-digit year in ISO format, which are negative for BCE years.

Note

  • When a date-only format is used, the associated time is assumed to be midnight on that day.

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

  • For more details about valid ranges, number of digits, and best practices, see Additional Information About Using Date, Time, and Timestamp Formats.

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                        |
-----------------------------------+
Copy
SELECT TO_VARCHAR('2013-04-05'::date, 'mon dd, yyyy');

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

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.