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.

TRY_CAST

## Syntax¶

CAST( <source_expr> AS <target_data_type> )

<source_expr> :: <target_data_type>


## 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 |
+---------------------------------+


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


Convert a number to an integer:

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


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