A BOOLEAN value can be cast explicitly to any of the string types. The result is 'true',
'false', or null. Conversely, string types can be cast to BOOLEAN. However, an error
is raised if the string value is not 'true', 'false', 'unknown', or null. Casting 'unknown' to
boolean results in a null value.

Conversions from numeric types

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.

Conversions from and to bit strings

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 cast to and from other types.

Conversions of date/time values

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.

Conversions of XML values

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.

Examples

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)