CAST
Given a valid expression and a target data type, the CAST function converts the expression into the specified data type.
Syntax
CAST (expression AS target_data_type)
Remarks
• The expression argument specifies what is to be converted to the target data type.
• If the input expression is NULL, the output is NULL. If the input expression is an empty string, the output is an empty string. In all other cases, the output type is the same as that of the target data type.
• Target data types can include length, precision, and scale arguments.
• You can use BLOB or CLOB data types in this function.
• When you convert a DECIMAL to an INTEGER, the resulting value is truncated rather than rounded. (For example, 15.99 is converted to 15.)
• The CAST function can truncate strings without issuing an error. For example, CAST ('30000' AS INTEGER) produces an integer (30000) with no error.
• The CAST function issues a runtime error if you cast a string '30000' to TINYINT, because the TINYINT data type cannot accommodate that large a number, and no meaningful truncation can be applied. In such a case, CAST proceeds normally only if all the values of the integer column are valid values for the TINYINT data type.
• You can use the CAST function to truncate strings and round down decimals to integers.
Note: For a function to round a decimal up to the next integer, see
CEILING.
• All INTERVALs can be cast to CHAR and VARCHAR and vice versa.
• Interval years, months, days, hour, minute, or seconds can only be cast to identical interval units. Errors are thrown if any data loss occurs. (See examples below table.)
• CAST from character string values to DATE, TIME, or TIMESTAMP requires that the input values be in one of these ISO formats:
— CAST to DATE—‘YYYY-MM-DD’ input value format
— CAST to TIME—‘HH24:MI:SS’ input value format (plus optional fractional seconds with a decimal point before them)
— CAST to TIMESTAMP—‘YYYY-MM-DD HH24:MI:SS’ input value format (plus optional fractional seconds with a decimal point before them)
If the values are not in these formats, you can use alternative data conversion functions such as
TO_DATE,
TO_TIMESTAMP or
PARSE_DATE,
PARSE_TIMESTAMP, and so on. Some of these functions may not be pushed, and the query itself might not be pushed, as a result of using these functions.
The following table shows the output type that results for each combination of input expression type and target data type.
Data Type of expression | Target Data Type | Output Type |
BIGINT, CHAR, DECIMAL, FLOAT, INTEGER, LONGVARCHAR, NUMERIC, REAL, SMALLINT, TINYINT, VARCHAR | BIGINT, DECIMAL, FLOAT, INTEGER, NUMERIC, REAL, SMALLINT, TINYINT | Target data type. |
NULL | BIGINT, CHAR, DATE, DECIMAL, FLOAT, LONGVARCHAR, NULL, NUMERIC, INTEGER, REAL, SMALLINT, TIME, TIMESTAMP, VARCHAR | NULL |
NULL<Data_Type1> | <Any_Data_Type2> | NULL<Data_Type1> |
BIGINT, CHAR, DATE, DECIMAL, FLOAT, INTEGER, LONGVARCHAR, NUMERIC, REAL, SMALLINT, TIME, TIMESTAMP, TINYINT, VARCHAR | CHAR, LONGVARCHAR, VARCHAR | Target data type |
DATE, TIMESTAMP | DATE | DATE |
TIME, TIMESTAMP | TIME | TIME |
BIGINT, CHAR, INTEGER, LONGVARCHAR, SMALLINT, TIMESTAMP, TINYINT, VARCHAR | TIMESTAMP | TIMESTAMP |
Example (Simple CAST Function)
SELECT products.UnitPrice, CAST (products.UnitPrice AS INTEGER) Price
FROM /shared/examples/ds_inventory/products products
Example (Target Data Type Includes Length)
CAST (Orders_Qry.ShipPostalCode AS CHAR(5))
Examples (With BLOB or CLOB)
CAST (myBlob AS VARBINARY(size))
CAST (myVarBinary AS BLOB)
CAST (myClob AS VARCHAR(size))
CAST (myVarChar AS CLOB)
Examples (Casting to Different Data Types)
CAST (INTERVAL '23' MONTH AS INTERVAL YEAR)
This returns an error (11 months lost).
CAST (INTERVAL '23' MONTH AS VARCHAR)
This returns 23 with a data type of VARCHAR.
CAST (INTERVAL '10' YEAR AS INTERVAL MONTH(3))
This returns the interval in months (120).