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