Snowflake CAST and TRY_CAST: Converting Data Types
In Snowflake, the CAST
function and ::
operator convert data from one type to another, ensuring that data fits the required format for operations or consistency in databases. If a conversion is not possible, an error is usually thrown. However, TRY_CAST
provides a safer alternative by returning NULL instead of an error if the conversion fails.
Syntax
CAST
Syntax
_10CAST(source_expr AS target_data_type)
::
Operator Syntax
_10source_expr :: target_data_type
TRY_CAST
Syntax
_10TRY_CAST(source_expr AS target_data_type)
Parameters
- source_expr: The value or expression you want to convert.
- target_data_type: The data type to which you want the expression converted.
Usage Notes
CAST
and::
will throw an error if the conversion isn’t possible.TRY_CAST
handles potential errors by returningNULL
instead of failing.TRY_CAST
works only for string expressions into specific types like VARCHAR, NUMBER, DATE, BOOLEAN, and TIMESTAMP variations.
Examples
CAST
Input Data
_11CREATE OR REPLACE TABLE input_data (_11 varchar_value VARCHAR,_11 number_value NUMBER(5, 4),_11 timestamp_value TIMESTAMP_11);_11_11INSERT INTO input_data VALUES (_11 '9.8765',_11 1.2345,_11 '2024-05-09 14:32:29.135 -0700'_11);
varchar_value | number_value | timestamp_value |
---|---|---|
9.8765 | 1.2345 | 2024-05-09 14:32:29.135 |
Convert a String to a Number with Specified Scale (2)
_10SELECT CAST(varchar_value AS NUMBER(5,2)) AS converted_number_10FROM input_data;
converted_value |
---|
9.88 |
Using :: for Quick Conversion
_10SELECT varchar_value::NUMBER(6,5) AS converted_number_10FROM input_data;
converted_value |
---|
9.87650 |
Convert a Number to an Integer
_10SELECT CAST(number_value AS INTEGER) AS converted_integer_10FROM input_data;
converted_integer |
---|
1 |
TRY_CAST
Safe Conversion with TRY_CAST
_10-- Correct conversion_10SELECT TRY_CAST('2024-01-01' AS DATE) AS safe_date;
safe_date |
---|
2024-01-01 |
_10-- This will return NULL because the format is incorrect_10SELECT TRY_CAST('not-a-date' AS DATE) AS safe_date;
safe_date |
---|
NULL |
_10-- This will return an ERROR because the format is incorrect_10SELECT CAST('not-a-date' AS DATE) AS safe_date;
Special Conversion Cases
- Handle Invalid Date Formats
_10SELECT TRY_CAST('05-Mar-2016' AS TIMESTAMP) AS valid_timestamp; -- Returns 2016-03-05 00:00:00.000_10SELECT TRY_CAST('05/16' AS TIMESTAMP) AS invalid_timestamp; -- Returns NULL
- Convert Strings
_10SELECT TRY_CAST('ABCD' AS CHAR(2)) AS too_long; -- Returns NULL because 'ABCD' is longer than 2 characters_10SELECT TRY_CAST('ABCD' AS VARCHAR(10)) AS fits_fine; -- Returns 'ABCD'
![Y42 SQL completion user interface](/learn/_next/image?url=%2Flearn%2Fimages%2Fcta%2Fsql-completion.webp&w=3840&q=100)