Data type conversion¶

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.

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;

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

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

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

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

As another example, consider the following statement:

SELECT -0.0::FLOAT::BOOLEAN;

You might expect this to be interpreted as:

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

Therefore, it would be expected 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);

Therefore, the query results in an error message because the unary minus can’t be applied to a BOOLEAN.

Data types that can be cast¶

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

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

Usage notes¶

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

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

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

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

• 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 (for example, 'a string') and string arguments that aren’t constants to the type NUMBER(18,5).

  For numeric string arguments that aren’t constants, if NUMBER(18,5) isn’t sufficient to represent the numeric value, then 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 a FLOAT value to an INTEGER value rounds the value.

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

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

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

  • Truncate a VARCHAR value. For example, Snowflake doesn’t 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 before 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 value to a TIMESTAMP_NTZ value causes the hour, minute, second, and fractional seconds to be set to 0.

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

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

  CREATE OR REPLACE TABLE convert_test_zeros (
    varchar1 VARCHAR,
    float1 FLOAT,
    variant1 VARIANT);
  
  INSERT INTO convert_test_zeros 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 convert_test_zeros;
  

  Copy

  +----------+-----------------+---------------------------------+
  | VARCHAR1 | FLOAT1::VARCHAR | VARIANT1:"LOAN NUMBER"::VARCHAR |
  |----------+-----------------+---------------------------------|
  | 5.000    | 5               | 5                               |
  +----------+-----------------+---------------------------------+
  

• Some operations can return different data types, depending upon a conditional expression. For example, the following IFNULL calls return slightly different data types depending upon the input values:

  SELECT SYSTEM$TYPEOF(IFNULL(12.3, 0)),
         SYSTEM$TYPEOF(IFNULL(NULL, 0));
  

  Copy

  +--------------------------------+--------------------------------+
  | SYSTEM$TYPEOF(IFNULL(12.3, 0)) | SYSTEM$TYPEOF(IFNULL(NULL, 0)) |
  |--------------------------------+--------------------------------|
  | NUMBER(3,1)[SB1]               | NUMBER(1,0)[SB1]               |
  +--------------------------------+--------------------------------+
  

  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 (for example, 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.