Snowflake
Convert Data Types

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

_10
CAST(source_expr AS target_data_type)

  • :: Operator Syntax

_10
source_expr :: target_data_type

  • TRY_CAST Syntax

_10
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


_11
CREATE OR REPLACE TABLE input_data (
_11
varchar_value VARCHAR,
_11
number_value NUMBER(5, 4),
_11
timestamp_value TIMESTAMP
_11
);
_11
_11
INSERT INTO input_data VALUES (
_11
'9.8765',
_11
1.2345,
_11
'2024-05-09 14:32:29.135 -0700'
_11
);

varchar_valuenumber_valuetimestamp_value
9.87651.23452024-05-09 14:32:29.135

Convert a String to a Number with Specified Scale (2)


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

converted_value
9.88

Using :: for Quick Conversion


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

converted_value
9.87650

Convert a Number to an Integer


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

converted_integer
1

TRY_CAST

Safe Conversion with TRY_CAST


_10
-- Correct conversion
_10
SELECT 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
_10
SELECT TRY_CAST('not-a-date' AS DATE) AS safe_date;

safe_date
NULL

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

Special Conversion Cases

  • Handle Invalid Date Formats

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

  • Convert Strings

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

Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started