1.0

Character string (CLOBSyntax in CREATE TABLEExample on page 575, CHARSyntax in CREATE TABLE
on page 574, VARCHARSyntax in CREATE TABLE on page 583, LONG VARCHAR on page 579)
•
• Bit string (BLOBSyntax in CREATE TABLEExample on page 574, CHAR FOR BIT DATASyntax in
CREATE TABLE on page 575, VARCHAR FOR BIT DATASyntax in CREATE TABLE on page 583, LONG
VARCHAR FOR BIT DATA on page 579)
• date/time
• DATEExample on page 576
• TIMEExample on page 581
• TIMESTAMPExamples on page 582
Conversions to and from logical types
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 on page 548 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)))
-- cast a double as a decimal
SELECT CAST (FLYING_TIME AS DECIMAL(5,2))
FROM FLIGHTS
vFabric SQLFire User's Guide516
vFabric SQLFire Reference