The CAST function converts a value from one data type to another and provides a data type to a dynamic parameter (?) or a NULL value.
CAST expressions are permitted anywhere expressions are permitted.
CAST ( [ Expression | NULL | ? ] AS Datatype)
The data type to which you are casting an expression is the target type. The data type of the expression from which you are casting is the source type.
Types | S |
I |
B |
D |
R |
D |
F |
C |
V |
L |
C |
V |
L |
C |
B |
D |
T |
T |
X |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SMALLINT | Y | Y | Y | Y | Y | Y | Y | Y | - | - | - | - | - | - | - | - | - | - | - |
INTEGER | Y | Y | Y | Y | Y | Y | Y | Y | - | - | - | - | - | - | - | - | - | - | - |
BIGINT | Y | Y | Y | Y | Y | Y | Y | Y | - | - | - | - | - | - | - | - | - | - | - |
DECIMAL | Y | Y | Y | Y | Y | Y | Y | Y | - | - | - | - | - | - | - | - | - | - | - |
REAL | Y | Y | Y | Y | Y | Y | Y | - | - | - | - | - | - | - | - | - | - | - | - |
DOUBLE | Y | Y | Y | Y | Y | Y | Y | - | - | - | - | - | - | - | - | - | - | - | - |
FLOAT | Y | Y | Y | Y | Y | Y | Y | - | - | - | - | - | - | - | - | - | - | - | - |
CHAR | Y | Y | Y | Y | - | - | - | Y | Y | Y | - | - | - | Y | - | Y | Y | Y | - |
VARCHAR | Y | Y | Y | Y | - | - | - | Y | Y | Y | - | - | - | Y | - | Y | Y | Y | - |
LONG VARCHAR | - | - | - | - | - | - | - | Y | Y | Y | - | - | - | Y | - | - | - | - | - |
CHAR FOR BIT DATA | - | - | - | - | - | - | - | - | - | - | Y | Y | Y | Y | Y | - | - | - | - |
VARCHAR FOR BIT DATA | - | - | - | - | - | - | - | - | - | - | Y | Y | Y | Y | Y | - | - | - | - |
LONG VARCHAR FOR BIT DATA | - | - | - | - | - | - | - | - | - | - | Y | Y | Y | Y | Y | - | - | - | - |
CLOB | - | - | - | - | - | - | - | Y | Y | Y | - | - | - | Y | - | - | - | - | - |
BLOB | - | - | - | - | - | - | - | - | - | - | - | - | - | - | Y | - | - | - | - |
DATE | - | - | - | - | - | - | - | Y | Y | - | - | - | - | - | - | Y | - | - | - |
TIME | - | - | - | - | - | - | - | Y | Y | - | - | - | - | - | - | - | Y | - | - |
TIMESTAMP | - | - | - | - | - | - | - | Y | Y | - | - | - | - | - | - | Y | Y | Y | - |
XML | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | Y |
If a conversion is valid, CASTs are allowed. Size incompatibilities between the source and target types might cause runtime errors.
A numeric type can be converted to any other numeric type. If the target type cannot represent the non-fractional component without truncation, an exception is raised. If the target numeric cannot represent the fractional component (scale) of the source numeric, then the source is silently truncated to fit into the target. For example, casting 763.1234 as INTEGER yields 763.
Bit strings can be converted to other bit strings, but not character strings. Strings that are converted to bit strings are padded with trailing zeros to fit the size of the target bit string. The BLOB type is more limited and requires explicit casting. In most cases the BLOB type cannot be casted to and from other types.
A date/time value can always be converted to and from a TIMESTAMP. If a DATE is converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP is always 00:00:00. If a TIME data value is converted to a TIMESTAMP, the DATE component is set to the value of CURRENT_DATE at the time the CAST is executed. If a TIMESTAMP is converted to a DATE, the TIME component is silently truncated. If a TIMESTAMP is converted to a TIME, the DATE component is silently truncated.
SELECT CAST (miles AS INT) FROM Flights -- convert timestamps to text INSERT INTO mytable (text_column) VALUES (CAST (CURRENT_TIMESTAMP AS VARCHAR(100))) -- you must cast NULL as a data type to use it SELECT airline FROM Airlines UNION ALL VALUES (CAST (NULL AS CHAR(2))) -- cast a double as a decimal SELECT CAST (FLYING_TIME AS DECIMAL(5,2)) FROM FLIGHTS -- cast a SMALLINT to a BIGINT VALUES CAST (CAST (12 as SMALLINT) as BIGINT)
An XML value cannot be converted to any non-XML type using an explicit or implicit CAST. Use the XMLSERIALIZE operator to convert an XML type to a character type.