Conversion Operations#
CAST#
Syntax
CAST(<source_expr> AS <target_data_type>)
Description
Converts a value of one data type into another data type.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 314159.07 |
| 5535.79 |
Applied formula:
CAST()
Output:
| VALUE_CAST |
|---|
| 314159.0 |
| 5536.0 |
SQL Editor Example
Convert a string containing a number to a decimal with specified scale (2):
select cast('1.2345' as decimal(15,2));
Output
| CAST('1.2345' AS DECIMAL(15,2)) |
|---|
| 1.23 |
TRY_CAST#
Syntax
TRY_CAST(<string_expr> AS <target_data_type>)
Description
Converts a String value into another data type, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Examples
Example for valid values (STRING to DATE), given the following table:
| COLUMN_1 |
|---|
| 1995-10-13 |
| 1992-07-21 |
| 1997-02-19 |
Applied formula:
TRY_CAST(COLUMN_1 AS DATE)
Output:
| COLUMN_2 |
|---|
| 1995-10-13 |
| 1992-07-21 |
| 1997-02-19 |
Example for invalid values (STRING to INTEGER), given the following table:
| COLUMN_1 |
|---|
| 1995-10-13 |
| 1992-07-21 |
| 1997-02-19 |
Applied formula:
TRY_CAST(COLUMN_1 AS INTEGER)
Output:
| COLUMN_2 |
|---|
SQL Editor Example
Example for valid converting a STRING to a DATE:
select try_cast('1995-10-13' as date)
from
ORDERS
Output
| TRY_CAST(COLUMN_1 AS DATE) |
|---|
| 1995-10-13 |
Example for invalid converting a STRING to an INTEGER:
select try_cast('1995-10-13' as integer)
from
ORDERS
Output
| TRY_CAST |
|---|
TRY_PARSE_JSON#
Syntax
TRY_PARSE_JSON(<string_expr>)
Description
Parses an input String as a JSON document, producing a VARIANT value. Results in NULL if there is an error during parsing.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| 1234567890 |
Applied formula:
TRY_PARSE_JSON(VALUE)
Output:
| VALUE_2 |
|---|
| 1234567890 |
SQL Editor Examples
select try_parse(CUSTOMER_ID)
from
CUSTOMER;
Output
| TRY_PARSE_JSON(CUSTOMER_ID) |
|---|
| 1234567890 |
If there is an error during the parsing the output would be:
| TRY_PARSE_JSON(CUSTOMER_ID) |
|---|
TRY_TO_BOOLEAN#
Syntax
TRY_TO_BOOLEAN(<string_expr>)
Description
Converts a String value into Boolean value, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| VALUE |
|---|
| false |
| true |
Applied formula:
TRY_TO_BOOLEAN(VALUE)
Output:
| VALUE_2 |
|---|
| false |
| true |
If there is an error during the conversion the output would be:
| VALUE_2 |
|---|
SQL Editor Examples
select TRY_TO_BOOLEAN(DELIVERED)
from
DATA;
Output
| TRY_TO_BOOLEAN(DELIVERED) |
|---|
| true |
| false |
If there is an error during the parsing the output would be:
| TRY_TO_BOOLEAN(DELIVERED) |
|---|
TRY_TO_DATE#
Syntax
TRY_TO_DATE(<string_expr> [, <format>])
Description
Converts a String value into Date, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| ORDERDATE |
|---|
| 1995-10-07 |
| 1996-01-07 |
Applied formula:
TRY_TO_DATE(ORDERDATE)
Output:
| ORDERDATE_2 |
|---|
| 1995-10-07 |
| 1996-01-07 |
If there is an error during the conversion the output would be:
| ORDERDATE_2 |
|---|
SQL Editor Examples
select TRY_TO_DATE(ORDERDATE)
from
ORDERS;
Output
| TRY_TO_ORDER(ORDERDATE) |
|---|
| 1995-10-07 |
| 1996-01-07 |
If there is an error during the parsing the output would be:
| TRY_TO_ORDER(ORDERDATE) |
|---|
TRY_TO_DECIMAL#
Syntax
TRY_TO_DECIMAL(<string_expr> [, <format> ] [, <precision> [, <scale> ]])
Description
Converts a String value into fixed-point number value, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| ORDERDATE |
|---|
| 1995-10-07 |
| 1996-01-07 |
Applied formula:
TRY_TO_DATE(ORDERDATE)
Output:
| ORDERDATE_2 |
|---|
| 1995-10-07 |
| 1996-01-07 |
If there is an error during the conversion the output would be:
| ORDERDATE_2 |
|---|
SQL Editor Examples
select TRY_TO_DATE(ORDERDATE)
from
ORDERS;
Output
| TRY_TO_DATE(ORDERDATE) |
|---|
| 1995-10-07 |
| 1996-01-07 |
If there is an error during the parsing the output would be:
| TRY_TO_DATE(ORDERDATE) |
|---|
TRY_TO_DOUBLE#
Syntax
TRY_TO_DOUBLE(<string_expr>)
Description
Converts a String value into double-precision floating-point value, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| PRICE |
|---|
| 26419.72 |
| 255011.51 |
Applied formula:
TRY_TO_DOUBLE(PRICE)
Output:
| PRICE_2 |
|---|
| 26419.72 |
| 255011.51 |
If there is an error during the conversion the output would be:
| PRICE_2 |
|---|
SQL Editor Examples
select TRY_TO_DOUBLE(PRICE)
from
ORDERS;
Output
| TRY_TO_DOUBLE(PRICE) |
|---|
| 26419.72 |
| 255011.51 |
If there is an error during the parsing the output would be:
| TRY_TO_DOUBLE(PRICE) |
|---|
TRY_TO_NUMBER#
Syntax
TRY_TO_NUMBER(<string_expr> [, <format> ] [, <precision> [, <scale> ]])
Description
Converts a String value into fixed-point number value, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| PRICE |
|---|
| 26419.72 |
| 255011.51 |
Applied formula:
TRY_TO_NUMBER(PRICE)
Output:
| PRICE_2 |
|---|
| 26419.72 |
| 255011.51 |
If there is an error during the conversion the output would be:
| PRICE_2 |
|---|
SQL Editor Examples
select TRY_TO_NUMBER(PRICE)
from
ORDERS;
Output
| TRY_TO_NUMBER(PRICE) |
|---|
| 26419.72 |
| 255011.51 |
If there is an error during the parsing the output would be:
| TRY_TO_NUMBER(PRICE) |
|---|
TRY_TO_NUMERIC#
Syntax
TRY_TO_NUMERIC(<string_expr> [, <format> ] [, <precision> [, <scale> ]])
Description
Converts a String value into fixed-point number value, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| PRICE |
|---|
| 26419.72 |
| 255011.51 |
Applied formula:
TRY_TO_NUMERIC(PRICE)
Output:
| PRICE_2 |
|---|
| 26420 |
| 255012 |
If there is an error during the conversion the output would be:
| PRICE_2 |
|---|
SQL Editor Examples
select TRY_TO_NUMERIC(PRICE)
from
ORDERS;
Output
| TRY_TO_NUMERIC(PRICE) |
|---|
| 26420 |
| 255012 |
If there is an error during the parsing the output would be:
| TRY_TO_NUMERIC(PRICE) |
|---|
TRY_TO_TIME#
Syntax
TRY_TO_TIME(<string_expr> [, <format>])
Description
Converts a String value into Time, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| TIME |
|---|
| 12:30:00 |
| 16:40:00 |
Applied formula:
TRY_TO_TIME(TIME)
Output:
| TIME_2 |
|---|
| 12:30:00 |
| 16:40:00 |
If there is an error during the conversion the output would be:
| TIME_2 |
|---|
SQL Editor Examples
select TRY_TO_TIME(TIME)
from
ORDERS;
Output
| TRY_TO_TIME(TIME) |
|---|
| 12:30:00 |
| 16:40:00 |
If there is an error during the parsing the output would be:
| TRY_TO_TIME(TIME) |
|---|
TRY_TO_TIMESTAMP#
Syntax
TRY_TO_TIMESTAMP(<string_expr> [, <format>])
Description
Converts a String value into Timestamp, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| TIMESTAMP |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
Applied formula:
TRY_TO_TIMESTAMP(TIMESTAMP)
Output:
| TIMESTAMP_2 |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
If there is an error during the conversion the output would be:
| TIMESTAMP_2 |
|---|
SQL Editor Examples
select TRY_TO_TIMESTAMP(TIMESTAMP)
from
ORDERS;
Output
| TRY_TO_TIMESTAMP(TIMESTAMP) |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
If there is an error during the parsing the output would be:
| TRY_TO_TIMESTAMP(TIMESTAMP) |
|---|
TRY_TO_TIMESTAMP_LTZ#
Syntax
TRY_TO_TIMESTAMP_LTZ(<string_expr> [, <format>])
Description
Converts a String value into Timestamp, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| TIMESTAMP |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
Applied formula:
TRY_TO_TIMESTAMP_LTZ(TIMESTAMP)
Output:
| TIMESTAMP_LTZ_2 |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
If there is an error during the conversion the output would be:
| TIMESTAMP_LTZ_2 |
|---|
SQL Editor Examples
select TRY_TO_TIMESTAMP_LTZ(TIMESTAMP)
from
ORDERS;
Output
| TRY_TO_TIMESTAMP_LTZ(TIMESTAMP) |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
If there is an error during the parsing the output would be:
| TRY_TO_TIMESTAMP_LTZ(TIMESTAMP) |
|---|
TRY_TO_TIMESTAMP_NTZ#
Syntax
TRY_TO_TIMESTAMP_NTZ(<string_expr> [, <format>])
Description
Converts a String value into Timestamp, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| TIMESTAMP |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
Applied formula:
TRY_TO_TIMESTAMP_NTZ(TIMESTAMP)
Output:
| TIMESTAMP_NTZ_2 |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
If there is an error during the conversion the output would be:
| TIMESTAMP_NTZ_2 |
|---|
SQL Editor Examples
select TRY_TO_TIMESTAMP_NTZ(TIMESTAMP)
from
ORDERS;
Output
| TRY_TO_TIMESTAMP_NTZ(TIMESTAMP) |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
If there is an error during the parsing the output would be:
| TRY_TO_TIMESTAMP_NTZ(TIMESTAMP) |
|---|
TRY_TO_TIMESTAMP_TZ#
Syntax
TRY_TO_TIMESTAMP_TZ(<string_expr> [, <format>])
Description
Converts a String value into Timestamp, but returns a NULL value instead of raising an error when the conversion can not be performed.
View examples
Formula Editor Example
Given the following table:
| TIMESTAMP |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
Applied formula:
TRY_TO_TIMESTAMP_TZ(TIMESTAMP)
Output:
| TIMESTAMP_TZ_2 |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
If there is an error during the conversion the output would be:
| TIMESTAMP_TZ_2 |
|---|
SQL Editor Examples
select TRY_TO_TIMESTAMP_TZ(TIMESTAMP)
from
ORDERS;
Output
| TRY_TO_TIMESTAMP_TZ(TIMESTAMP) |
|---|
| 2016-09-04 19:38:16 |
| 2016-09-04 19:38:21 |
If there is an error during the parsing the output would be:
| TRY_TO_TIMESTAMP_TZ(TIMESTAMP) |
|---|