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 , ::
Any data type	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
Boolean data type	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 cannot be performed:

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. For example, when set to `1980`, values of `79` and `80` are parsed as `2079` and `1980` respectively.
`MM`	Two-digit month (`01` = January, and so on).
`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¶

Convert a string to a date using a specified input format of dd/mm/yyyy. The display format for dates in the output is determined by the DATE_OUTPUT_FORMAT session parameter (default YYYY-MM-DD).

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

Copy

+-----------------------------------+
| TO_DATE('3/4/2024', 'DD/MM/YYYY') |
|-----------------------------------|
| 2024-04-03                        |
+-----------------------------------+

Convert a date to a string, and specify a date output format of mon dd, yyyy.

SELECT TO_VARCHAR('2024-04-05'::DATE, 'mon dd, yyyy');

Copy

+------------------------------------------------+
| TO_VARCHAR('2024-04-05'::DATE, 'MON DD, YYYY') |
|------------------------------------------------|
| Apr 05, 2024                                   |
+------------------------------------------------+

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.