# 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 , 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¶

TO_CHAR , TO_VARCHAR, TO_DATE , DATE, TRY_TO_DATE, TO_TIME , TIME, TRY_TO_TIME, and all the TO_TIMESTAMP / TO_TIMESTAMP_* and TRY_TO_TIMESTAMP / TRY_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.