Data type conversion

In many cases, a value of one data type can be converted to another data type. For example, an INTEGER can be converted to a floating-point data type. Converting a data type is called casting.

Explicit casting vs implicit casting

Users can explicitly convert a value from one data type to another. This is called explicit casting.

In some situations, Snowflake converts a value to another data type automatically. This is called implicit casting or coercion.

Explicit casting

Users can explicitly cast a value by using any of the following options:

  • The CAST function.

  • The :: operator (called the cast operator).

  • The appropriate SQL function (e.g. TO_DOUBLE).

For example:

SELECT CAST('2022-04-01' AS DATE);

SELECT '2022-04-01'::DATE;

SELECT TO_DATE('2022-04-01');
Copy

Casting is allowed in most contexts in which a general expression is allowed, including the WHERE clause. For example:

SELECT date_column
    FROM log_table
    WHERE date_column >= '2022-04-01'::DATE;
Copy

Implicit casting (“coercion”)

Coercion occurs when a function (or operator) requires a data type that is different from, but compatible with, the arguments (or operands).

  • Examples for functions or stored procedures:

    • The following code coerces the INTEGER value in column my_integer_column to FLOAT so that the value can be passed to the function my_float_function(), which expects a FLOAT:

      SELECT my_float_function(my_integer_column)
          FROM my_table;
      
      Copy
  • Examples for operators:

    • The following code coerces the INTEGER value 17 to VARCHAR so that the values can be concatenated by using the || operator:

      SELECT 17 || '76';
      
      Copy

      The result of this SELECT statement is the string '1776'.

    • The following statement coerces the INTEGER value in column my_integer_column to FLOAT so that the value can be compared to the value my_float_column using the < comparison operator:

      SELECT ...
          FROM my_table
          WHERE my_integer_column < my_float_column;
      
      Copy

Not all contexts (e.g. not all operators) support coercion.

Casting and precedence

When casting inside an expression, the code must take into account the precedence of the cast operator relative to other operators in the expression.

Consider the following example:

SELECT height * width::VARCHAR || " square meters"
    FROM dimensions;
Copy

The cast operator has higher precedence than the arithmetic operator * (multiply), so the statement is interpreted as:

... height * (width::VARCHAR) ...
Copy

To cast the result of the expression height * width, use parentheses, as shown below:

SELECT (height * width)::VARCHAR || " square meters"
    FROM dimensions;
Copy

As another example, consider the following statement:

SELECT -0.0::FLOAT::BOOLEAN;
Copy

You might expect this to be interpreted as:

SELECT (-0.0::FLOAT)::BOOLEAN;
Copy

and therefore to return FALSE (0 = FALSE, 1 = TRUE).

However, the cast operator has higher precedence than the unary minus (negation) operator, so the statement is interpreted as:

SELECT -(0.0::FLOAT::BOOLEAN);
Copy

and therefore results in an error message because the unary minus cannot be applied to a BOOLEAN.

Data types that can be cast

The table below shows the valid data type conversions in Snowflake. The table also shows which coercions Snowflake can perform automatically.

Note

Internally, the CAST function and the :: operator call the appropriate conversion function. For example, if you cast a NUMBER to a BOOLEAN, then Snowflake calls the TO_BOOLEAN function. The usage notes for each conversion function apply when the function is called indirectly via a cast, as well as when the function is called directly. For example, if you execute CAST(my_decimal_column as BOOLEAN), the rules for calling TO_BOOLEAN with a DECIMAL value apply. For convenience, the “Notes” column in the table below includes links to the relevant conversion functions.

For details on conversions between semi-structured types and structured types, see Converting structured and semi-structured types.

Source Data Type

Target Data Type

Castable

Coercible

Conversion Function

Notes

ARRAY

VARCHAR

TO_VARCHAR

VARIANT

TO_VARIANT

VECTOR

BINARY

VARCHAR

TO_VARCHAR

VARIANT

TO_VARIANT

BOOLEAN

NUMBER

TO_NUMBER

VARCHAR

TO_VARCHAR

For example, from TRUE to ‘true’.

VARIANT

TO_VARIANT

DATE

TIMESTAMP

TO_TIMESTAMP

VARCHAR

TO_VARCHAR

VARIANT

TO_VARIANT

FLOAT . (Floating-point numbers)

BOOLEAN

TO_BOOLEAN

For example, from 0.0 to FALSE.

NUMBER[(p,s)]

TO_NUMBER

VARCHAR

TO_VARCHAR

VARIANT

TO_VARIANT

GEOGRAPHY

VARIANT

TO_VARIANT

GEOMETRY

VARIANT

TO_VARIANT

NUMBER[(p,s)] . (Fixed-point numbers, including INTEGER)

BOOLEAN

TO_BOOLEAN

For example, from 0 to FALSE.

FLOAT

TO_DOUBLE

TIMESTAMP

TO_TIMESTAMP

[1]

VARCHAR

TO_VARCHAR

VARIANT

TO_VARIANT

OBJECT

ARRAY

TO_ARRAY

VARCHAR

TO_VARCHAR

VARIANT

TO_VARIANT

TIME

VARCHAR

TO_VARCHAR

VARIANT

TO_VARIANT

TIMESTAMP

DATE

TO_DATE , DATE

TIME

TO_TIME , TIME

VARCHAR

TO_VARCHAR

VARIANT

TO_VARIANT

VARCHAR

BOOLEAN

TO_BOOLEAN

For example, from ‘false’ to FALSE.

DATE

TO_DATE , DATE

FLOAT

TO_DOUBLE

For example, from ‘12.34’ to 12.34.

NUMBER[(p,s)]

TO_NUMBER

For example, from ‘12.34’ to 12.34.

TIME

TO_TIME , TIME

TIMESTAMP

TO_TIMESTAMP

VARIANT

TO_VARIANT

VARIANT

ARRAY

TO_ARRAY

BOOLEAN

TO_BOOLEAN

For example, from a VARIANT containing ‘false’ to FALSE.

DATE

TO_DATE , DATE

FLOAT

TO_DOUBLE

GEOGRAPHY

TO_GEOGRAPHY

NUMBER[(p,s)]

TO_NUMBER

OBJECT

TO_OBJECT

TIME

TO_TIME , TIME

TIMESTAMP

TO_TIMESTAMP

VARCHAR

TO_VARCHAR

VECTOR

The VARIANT must contain an ARRAY of type FLOAT or INT.

VECTOR

ARRAY

TO_ARRAY

Notes:

  • For each listed data type (e.g. FLOAT), the rules apply for all aliases for that data type (e.g. the rules for FLOAT apply to DOUBLE, which is an alias for FLOAT).

Usage notes

Except where stated otherwise, the following rules apply to both explicit casting and implicit casting.

  • Conversion depends not only upon the data type, but also the value, of the source. For example:

    • The VARCHAR ‘123’ can be converted to a numeric value, but the VARCHAR ‘xyz’ cannot be converted to a numeric value.

    • The ability to cast a specific value of type VARIANT depends upon the type of the data inside the VARIANT. For example, if the VARIANT contains a value of type TIME, then you cannot cast the VARIANT to a TIMESTAMP because you cannot cast TIME to TIMESTAMP.

  • If possible, pass in arguments of the same type. Avoid passing in arguments of different types.

  • If one of the arguments is a number, the function coerces non-numeric string arguments (e.g. 'a string') and string arguments that are not constants to the type NUMBER(18,5).

    For numeric string arguments that are not constants, if NUMBER(18,5) is not sufficient to represent the numeric value, you should cast the argument to a type that can represent the value.

  • For some pairs of data types, conversion can result in loss of precision. For example:

    • Converting FLOAT to INTEGER rounds the value.

    • Converting a value from fixed-point numeric (e.g. NUMBER(38, 0)) to floating point (e.g. FLOAT) can result in rounding or truncation if the fixed-point number cannot be precisely represented in a floating point number.

    • Converting a TIMESTAMP to a DATE removes the information about the time of day.

  • Although Snowflake converts values in some situations where loss of precision can occur, Snowflake does not allow conversion in other situations where a loss of precision would occur. For example, Snowflake does not allow conversion when conversion would:

    • Truncate a VARCHAR value. For example, Snowflake does not cast VARCHAR(10) to VARCHAR(5), either implicitly or explicitly.

    • Result in the loss of digits other than the least significant digits. For example, the following fails:

      select 12.3::FLOAT::NUMBER(3,2);
      
      Copy

    In this example, the number 12.3 has two digits prior to the decimal point, but the data type NUMBER(3,2) has room for only one digit before the decimal point.

  • When converting from a type with less precision to a type with more precision, conversion uses default values. For example, converting a DATE to a TIMESTAMP_NTZ causes the hour, minute, second, and fractional seconds to be set to 0.

  • When a FLOAT value is cast to a VARCHAR, trailing zeros are omitted.

    For example, the following statements create a table and insert a row that contains a VARCHAR, a FLOAT, and a VARIANT. The VARIANT is constructed from JSON that contains a floating-point value represented with trailing zeros.

    create or replace table tmp (
        varchar1 varchar, 
        float1 float, 
        variant1 variant
        );
    
    insert into tmp select '5.000', 5.000, parse_json('{"Loan Number": 5.000}');
    
    Copy

    The following SELECT statement explicitly casts both the FLOAT column and the FLOAT value inside the VARIANT column to VARCHAR. In each case, the VARCHAR contains no trailing zeros:

    select varchar1, 
           float1::varchar,
           variant1:"Loan Number"::varchar from tmp;
    +----------+-----------------+---------------------------------+
    | VARCHAR1 | FLOAT1::VARCHAR | VARIANT1:"LOAN NUMBER"::VARCHAR |
    |----------+-----------------+---------------------------------|
    | 5.000    | 5               | 5                               |
    +----------+-----------------+---------------------------------+
    
    Copy
  • Some operations can return different data types, depending upon a conditional expression. For example, the following COALESCE calls return slightly different data types depending upon the input values:

    select system$typeof(ifnull(12.3, 0)),
           system$typeof(ifnull(NULL, 0));
    
    +--------------------------------+--------------------------------+
    | SYSTEM$TYPEOF(IFNULL(12.3, 0)) | SYSTEM$TYPEOF(IFNULL(NULL, 0)) |
    +--------------------------------+--------------------------------+
    | NUMBER(3,1)[SB1]               | NUMBER(1,0)[SB1]               |
    +--------------------------------+--------------------------------+
    
    Copy

    If the expression has more than one possible data type, then Snowflake chooses the data type based on the actual result. (For more information about precision and scale in calculations, see Scale and precision in arithmetic operations.) If the query generates more than one result (e.g. multiple rows of results), then Snowflake chooses a data type that is capable of holding each of the individual results.

  • Some application programs, such as SnowSQL, and some graphical user interfaces, such as the Classic Console, apply their own conversion and formatting rules when displaying data. For example, SnowSQL displays BINARY values as a string that contains only hexadecimal digits; that string is generated by implicitly calling a conversion function. Therefore, the data that SnowSQL displays might not unambiguously indicate which data conversions Snowflake coerced.