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>

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 scale and precision (for numbers/decimals), the properties can be included.

Examples

Convert a string containing a number to a decimal with specified precision and scale:

SELECT CAST('1.2345' AS DECIMAL(15,2));

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

SELECT CAST('1.2345' AS DECIMAL(15,5));

+---------------------------------+
| CAST('1.2345' AS 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) |
|----------------------------------|
| Wed, 05 Mar 2014 00:00:00 -0800  |
+----------------------------------+