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

CAST(source_expr AS target_data_type)

:: Operator Syntax

source_expr :: target_data_type

TRY_CAST Syntax

TRY_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 returning NULL 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

CREATE OR REPLACE TABLE input_data (
    varchar_value VARCHAR,
    number_value NUMBER(5, 4),
    timestamp_value TIMESTAMP
);

INSERT INTO input_data VALUES (
    '9.8765',
    1.2345,
    '2024-05-09 14:32:29.135 -0700'
);

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)

SELECT CAST(varchar_value AS NUMBER(5,2)) AS converted_number
FROM input_data;

converted_value
9.88

Using :: for Quick Conversion

SELECT varchar_value::NUMBER(6,5) AS converted_number
FROM input_data;

converted_value
9.87650

Convert a Number to an Integer

SELECT CAST(number_value AS INTEGER) AS converted_integer
FROM input_data;

converted_integer
1

TRY_CAST

Safe Conversion with TRY_CAST

-- Correct conversion
SELECT TRY_CAST('2024-01-01' AS DATE) AS safe_date;

safe_date
2024-01-01

-- This will return NULL because the format is incorrect
SELECT TRY_CAST('not-a-date' AS DATE) AS safe_date;

safe_date
NULL

-- This will return an ERROR because the format is incorrect
SELECT CAST('not-a-date' AS DATE) AS safe_date;

Date 'not-a-date' is not recognized

Special Conversion Cases

Handle Invalid Date Formats

SELECT TRY_CAST('05-Mar-2016' AS TIMESTAMP) AS valid_timestamp; -- Returns 2016-03-05 00:00:00.000
SELECT TRY_CAST('05/16' AS TIMESTAMP) AS invalid_timestamp;  -- Returns NULL

Convert Strings

SELECT TRY_CAST('ABCD' AS CHAR(2)) AS too_long;  -- Returns NULL because 'ABCD' is longer than 2 characters
SELECT TRY_CAST('ABCD' AS VARCHAR(10)) AS fits_fine;  -- Returns 'ABCD'

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started