There are many occasions when a value with a given data type needs to be cast (changed) to a different data type or to the same data type with a different length, precision, or scale. Data type promotion, as described in Promotion of Data Types, is one example of when a value with one data type needs to be cast to a new data type. A data type that can be changed to another data type is castable from the source data type to the target data type.
The casting of one data type to another can occur implicitly or explicitly. You can use cast functions or CAST specifications to explicitly cast a data type. The database manager might implicitly cast data types during assignments that involve a distinct type (see Distinct Type Assignments). In addition, when you create a sourced user-defined function, the data types of the parameters of the source function must be castable to the data types of the function that you are creating (see CREATE FUNCTION).
If truncation occurs when a character or graphic string is cast to another data type, a warning occurs if any nonblank characters are truncated. This truncation behavior is unlike the assignment of character or graphic strings to a target when an error occurs if any nonblank characters are truncated.
For casts that involve a distinct type as either the data type to be cast
to or from, Table 8 shows the supported casts. For casts between built-in
data types, Table 9 shows the supported casts.
Table 8. Supported Casts When a Distinct Type is Involved
| Data Type ... | Is Castable to Data Type ... |
|---|---|
| Distinct type DT | Source data type of distinct type DT |
| Source data type of distinct type DT | Distinct type DT |
| Distinct type DT | Distinct type DT |
| Data type A | Distinct type DT where A is promotable to the source data type of distinct type DT (see Promotion of Data Types) |
| INTEGER | Distinct type DT if DT's source type is SMALLINT |
| DOUBLE | Distinct type DT if DT's source data type is REAL |
| VARCHAR or VARGRAPHIC | Distinct type DT if DT's source data type is CHAR or GRAPHIC |
When a distinct type that is not explicitly qualified with a collection name is involved in a cast, the database manager uses the SQL path to determine a collection name. The database manager chooses the name of the first collection in the SQL path that contains a distinct type by that name. For more information on the SQL path, see Collections and the SQL Path.
The following table describes the supported casts between data types:
Table 9. Supported Casts Between Built-In Data Types
| Source Data Type |
SMALLINT INTEGER BIGINT |
DECIMAL NUMERIC |
REAL DOUBLE |
CHAR VARCHAR CLOB |
GRAPHIC VARGRAPHIC DBCLOB | DATE | TIME | TIME STAMP | BLOB | ||
|---|---|---|---|---|---|---|---|---|---|---|---|
| SMALLINT | Y | Y | Y | Y | -- | -- | -- | -- | -- | ||
| INTEGER | Y | Y | Y | Y | -- | -- | -- | -- | -- | ||
| BIGINT | Y | Y | Y | Y | -- | -- | -- | -- | -- | ||
| DECIMAL | Y | Y | Y | Y | -- | -- | -- | -- | -- | ||
| NUMERIC | Y | Y | Y | Y | -- | -- | -- | -- | -- | ||
| REAL | Y | Y | Y | Y | -- | -- | -- | -- | -- | ||
| DOUBLE | Y | Y | Y | Y | -- | -- | -- | -- | -- | ||
| CHAR | Y | Y | Y | Y | * | Y | Y | Y | Y | ||
| VARCHAR | Y | Y | Y | Y | * | Y | Y | Y | Y | ||
| CLOB | Y | Y | Y | Y | * | -- | -- | -- | Y | ||
| GRAPHIC | -- | -- | -- | * | Y | -- | -- | -- | Y | ||
| VARGRAPHIC | -- | -- | -- | * | Y | -- | -- | -- | Y | ||
| DBCLOB | -- | -- | -- | * | Y | -- | -- | -- | Y | ||
| DATE | -- | -- | -- | Y** | -- | Y | -- | Y | -- | ||
| TIME | -- | -- | -- | Y** | -- | -- | Y | Y | -- | ||
| TIMESTAMP | -- | -- | -- | Y** | -- | Y | Y | Y | -- | ||
| BLOB | -- | -- | -- | -- | -- | -- | -- | -- | Y | ||
| |||||||||||
The following table describes the rules for casting to a data type:
Table 10. Rules for Casting to a Data Type
| Target Data Type | Source Data Type | Rules |
|---|---|---|
| SMALLINT | Any | See the SMALLINT scalar function. |
| INTEGER | Any | See the INTEGER scalar function. |
| BIGINT | Any | See the BIGINT scalar function. |
| DECIMAL | Any | See the DECIMAL scalar function. |
| NUMERIC | Any | See the ZONED scalar function. |
| REAL | Any | See the REAL scalar function. |
| DOUBLE | Any | See the DOUBLE scalar function. |
| CHAR | Any | See the CHAR scalar function. |
| VARCHAR | Any | See the VARCHAR scalar function. |
| CLOB | Any | See the CLOB scalar function. |
| GRAPHIC | Any | See the rules for string assignment to a host variable. |
| VARGRAPHIC | Any | See the rules for string assignment to a host variable. |
| DBCLOB | Any | See the DBCLOB scalar function. |
| DATE | Any | See the DATE scalar function. |
| TIME | Any | See the TIME scalar function. |
| TIMESTAMP | CHAR | See the TIMESTAMP scalar function, where one operand is specified. |
| TIMESTAMP | DATE | The timestamp is composed of the specified date and a time of 00:00:00. |
| TIMESTAMP | TIME | The timestamp is composed of the CURRENT_DATE and the specified time. |
| BLOB | Any | See the BLOB scalar function. |
| DATALINK | DATALINK | See the rules for DataLink assignments. |