- 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> )
[ RENAME FIELDS | ADD FIELDS ]
<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.
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 | +---------------------------------+
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 | +----------------------------------+