Categories:

Conversion Functions

CAST , ::¶

Converts a value of one data type into another data type. The semantics of CAST are the same as the semantics of the corresponding TO_ datatype conversion functions. If the cast is not possible, an error is raised. For more details, see the individual TO_ datatype conversion functions.

The :: operator provides alternative syntax for CAST.

See also:

TRY_CAST

Syntax¶

CAST( <source_expr> AS <target_data_type> )
  [ RENAME FIELDS | ADD FIELDS ]

<source_expr> :: <target_data_type>
Copy

Arguments¶

source_expr

Expression of any supported data type to be converted into a different data type.

target_data_type

The data type to which to convert the expression. If the data type supports additional properties, such as precision and scale (for numbers/decimals), the properties can be included.

RENAME FIELDS

For structured OBJECTs, specifies that you want to change the OBJECT to use different key-value pairs. The values in the original object are copied to the new key-value pairs in the order in which they appear.

For an example, see Example: Changing the Key Names in an OBJECT.

ADD FIELDS

For structured OBJECTs, specifies that you want to add key-value pairs to the OBJECT.

For an example, see Example: Adding Keys to an OBJECT.

The values for the newly added keys will be set to NULL. If you want to assign a value to these keys, call the OBJECT_INSERT function instead.

Usage Notes¶

  • If the scale is not sufficient to hold the input value, the function rounds the value.

  • If the precision is not sufficient to hold the input value, the function raises an error.

  • When numeric columns are explicitly cast to forms of the integer data type during a data unload to Parquet files, the data type of these columns in the Parquet files is INT. For more information, see Explicitly converting numeric columns to Parquet data types.

Examples¶

Convert a string containing a number to a decimal with specified scale (2):

SELECT CAST('1.2345' AS DECIMAL(15,2));
+---------------------------------+
| CAST('1.2345' AS DECIMAL(15,2)) |
|---------------------------------|
|                            1.23 |
+---------------------------------+
Copy

Convert the same string to a decimal with scale 5, using the :: notation:

SELECT '1.2345'::DECIMAL(15,5);
+-------------------------+
| '1.2345'::DECIMAL(15,5) |
|-------------------------|
|                 1.23450 |
+-------------------------+
Copy

Convert a number to an integer:

SELECT CAST(1.56 AS INTEGER);
+-----------------------+
| CAST(1.56 AS INTEGER) |
|-----------------------|
|                     2 |
+-----------------------+
Copy

Convert a string containing a date to a timestamp:

SELECT CAST('05-Mar-2014' AS TIMESTAMP);
+----------------------------------+
| CAST('05-MAR-2014' AS TIMESTAMP) |
|----------------------------------|
| 2014-03-05 00:00:00.000          |
+----------------------------------+
Copy