CAST function

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.

Syntax

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.

CAST conversions among SQL-92 data types

The following table shows each source data type and lists those data types to which the source type can be converted. If a conversion is valid, CASTs are allowed. All data types can cast to the same type. BLOB and XML types cna only be cast to themselves. Size incompatibilities between the source and target types might cause runtime errors.
Table 1. Supported Data Type Conversions
Source Data Type: Converts To:
BOOLEAN

CHAR
VARCHAR
LONGVARCHAR
CLOB

SMALLINT

INTEGER
BIGINT
DECIMAL
REAL
DOUBLE
FLOAT
CHAR

INTEGER

SMALLINT
BIGINT
DECIMAL
REAL
DOUBLE
FLOAT
CHAR

BIGINT

SMALLINT
INTEGER
DECIMAL
REAL
DOUBLE
FLOAT
CHAR

DECIMAL

SMALLINT
INTEGER
BIGINT
REAL
DOUBLE
FLOAT
HAR

REAL

SMALLINT
INTEGER
BIGINT
DECIMAL
DOUBLE
FLOAT

DOUBLE

SMALLINT
INTEGER
BIGINT
DECIMAL
REAL
FLOAT

FLOAT

SMALLINT
INTEGER
BIGINT
DECIMAL
REAL
DOUBLE

CHAR

BOOLEAN
SMALLINT
INTEGER
BIGINT
DECIMAL
VARCHAR
LONG VARCHAR
CLOB
DATE
TIME
TIMESTAMP

VARCHAR

BOOLEAN
SMALLINT
INTEGER
BIGINT
DECIMAL
CHAR
LONG VARCHAR
CLOB
DATE
TIME
TIMESTAMP

LONG VARCHAR

BOOLEAN
CHAR
VARCHAR
CLOB

CHAR FOR BIT DATA

VARCHAR FOR BIT DATA
LONG VARCHAR FOR BIT DATA
CLOB
BLOB

VARCHAR FOR BIT DATA

CHAR FOR BIT DATA
LONG VARCHAR FOR BIT DATA
CLOB
BLOB

LONG VARCHAR FOR BIT DATA

CHAR FOR BIT DATA
VARCHAR FOR BIT DATA
CLOB
BLOB

CLOB

BOOLEAN
CHAR
VARCHAR
LONG VARCHAR

BLOB

BLOB

DATE

CHAR
VARCHAR

TIME

CHAR
VARCHAR

TIMESTAMP

CHAR
VARCHAR
DATE
TIME

XML

XML

Notes

In this discussion, the GemFire XD SQL-92 data types are categorized as follows:

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 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
-- cast a SMALLINT to a BIGINT
VALUES CAST (CAST (12 as SMALLINT) as BIGINT)