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> )

<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.

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
Language: English