Categories: Conversion

CAST Preview

Converts a value of one data type to another data type. This function behaves similarly to the TO_<data_type> (i.e. TO_TIMESTAMP) functions.

Syntax

CAST(expression Any type, data_type Any type) → Type specified as data_type parameter

• expression: The expression that you want to convert
• data_type: The name of the data type that you want to convert the input expression to.

Examples

CAST example

SELECT CAST(3.14150 AS INTEGER)
-- 3

CAST example

SELECT CAST(.167 AS INTEGER)
-- 0

CAST example

SELECT CAST('2021-04-03' AS DATE)
-- 2021-04-03

CAST example

SELECT CAST(PARSE_JSON('42') AS INTEGER)
-- 42

CAST example

SELECT CAST(PARSE_JSON('42') AS VARCHAR)
-- 42

CAST example

SELECT CAST(PARSE_JSON('[1,2,3]') AS ARRAY<INTEGER>)
-- [1,2,3]

Usage Notes

Variant Types

When the source expression is a VARIANT, CAST(expression AS data_type) is equivalent to VARIANT_GET(expression, '$', data_type), extracting the root value and converting it to the target type.

Source	Target	Result
Scalar VARIANT	Compatible scalar type	Converted value
Scalar VARIANT	VARCHAR	String representation
Scalar VARIANT	Incompatible scalar type	Error
VARIANT<ARRAY>	Typed ARRAY (e.g., ARRAY<INTEGER>)	Converted array
VARIANT<STRUCT>	STRUCT	Converted struct
VARIANT<ARRAY> or VARIANT<STRUCT>	VARCHAR	Error
VARIANT	VARIANT	Passthrough
Non-VARIANT	VARIANT	Not supported (use TO_VARIANT)

To convert complex VARIANT values to a JSON string, use TO_JSON instead of CAST.