Casting between data types
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. The cast functions or CAST specification (see CAST specification) can be used to explicitly change 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 (sourced)).
If truncation occurs when a character or graphic string is cast to another data type, a warning occurs if any non-blank characters are truncated. This truncation behavior is similar to retrieval assignment of character or graphic strings (see Retrieval assignment:).
If truncation occurs when a binary string is cast to another data type, a warning occurs. This truncation behavior is similar to retrieval assignment of binary strings (see Retrieval assignment).
For casts that involve an array type, the
source and target data type must both be the same array type.
For casts that involve a distinct type as either the data type to be cast to or from, Table 1 shows the supported casts. For casts between built-in data types, Table 2 shows the supported casts.
| 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 | Distinct type DT if DT's source data type is CHAR or GRAPHIC |
| VARGRAPHIC | Distinct type DT if DT's source data type is GRAPHIC or CHAR |
| VARBINARY | Distinct type DT if DT's source data type is BINARY |
Character and graphic strings are only compatible for Unicode data. Character bit data and graphic strings are not compatible.
When a distinct type is involved in a cast, a cast function that was generated when the distinct type was created is used. How the database manager chooses the function depends on whether function notation or the CAST specification syntax is used. For details, see Function resolution, and CAST specification. Function resolution is used for both. However, in a CAST specification, when an unqualified distinct type is specified as the target data type, the database manager resolves the schema name of the distinct type and then uses that schema name to locate the cast function.
The following table describes the supported casts between built-in data types.
| Target Data Type → Source Data Type ↓ |
SMALLINT
|
INTEGER
|
BIGINT
|
DECIMAL
NUMERIC |
REAL
DOUBLE |
DECFLOAT
|
CHAR
VARCHAR CLOB |
GRAPHIC
VARGRAPHIC DBCLOB |
BINARY
VARBINARY BLOB |
DATE
|
TIME
|
TIMESTAMP
|
ROWID
|
DATALINK
|
XML
|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SMALLINT | Y | Y | Y | Y | Y | Y | Y | Y1 | — | — | — | — | — | — | — |
| INTEGER | Y | Y | Y | Y | Y | Y | Y | Y1 | — | — | — | — | — | — | — |
| BIGINT | Y | Y | Y | Y | Y | Y | Y | Y1 | — | — | — | — | — | — | — |
| DECIMAL | Y | Y | Y | Y | Y | Y | Y | Y1 | — | — | — | — | — | — | — |
| NUMERIC | Y | Y | Y | Y | Y | Y | Y | Y1 | — | — | — | — | — | — | — |
| REAL | Y | Y | Y | Y | Y | Y | Y | Y1 | — | — | — | — | — | — | — |
| DOUBLE | Y | Y | Y | Y | Y | Y | Y | Y1 | — | — | — | — | — | — | — |
| DECFLOAT | Y | Y | Y | Y | Y | Y | Y | Y1 | — | — | — | — | — | — | — |
| CHAR | Y | Y | Y | Y | Y | Y | Y | Y1 | Y | Y | Y | Y | Y | — | — |
| VARCHAR | Y | Y | Y | Y | Y | Y | Y | Y1 | Y | Y | Y | Y | Y | — | — |
| CLOB | Y | Y | Y | Y | Y | Y | Y | Y1 | Y | Y | Y | Y | Y | — | — |
| GRAPHIC | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y | — | Y1 | Y1 | Y1 | — | — | — |
| VARGRAPHIC | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y | — | Y1 | Y1 | Y1 | — | — | — |
| DBCLOB | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y | — | Y1 | Y1 | Y1 | — | — | — |
| BINARY | — | — | — | — | — | — | Y | — | Y | — | — | — | — | — | — |
| VARBINARY | — | — | — | — | — | — | Y | — | Y | — | — | — | — | — | — |
| BLOB | — | — | — | — | — | — | Y | — | Y | — | — | — | — | — | — |
| DATE | — | Y | Y | Y | — | — | Y | Y1 | — | Y | — | Y | — | — | — |
| TIME | — | Y | Y | Y | — | — | Y | Y1 | — | — | Y | Y | — | — | — |
| TIMESTAMP | — | — | Y | Y | — | — | Y | Y1 | — | Y | Y | Y | — | — | — |
| ROWID | — | — | — | — | — | — | Y | — | Y | — | — | — | Y | — | — |
| DATALINK | — | — | — | — | — | — | — | — | — | — | — | — | — | Y | — |
| XML | — | — | — | — | — | — | — | — | — | — | — | — | — | — | Y |
| Notes:
1 Conversion is only supported for Unicode graphic. If the other data type is FOR BIT DATA, conversion is not supported. |
|||||||||||||||
The following table describes the rules for casting to a data type:
| Target Data Type | Rules |
|---|---|
| SMALLINT | See SMALLINT. |
| INTEGER | See INTEGER or INT. |
| BIGINT | See BIGINT. |
| DECIMAL | See DECIMAL or DEC. |
| NUMERIC | See ZONED. |
| REAL | See REAL. |
| DOUBLE | See DOUBLE_PRECISION or DOUBLE. |
| DECFLOAT | See DECFLOAT |
| CHAR | See CHAR. |
| VARCHAR | See the VARCHAR. |
| CLOB | See CLOB. |
| GRAPHIC | If the source data type is a character string, see the rules for string assignment to a variable in Assignments and comparisons. Otherwise, see GRAPHIC. |
| VARGRAPHIC | If the source data type is a character string, see the rules for string assignment to a variable in Assignments and comparisons. Otherwise, see VARGRAPHIC. |
| DBCLOB | See DBCLOB. |
| BINARY | See BINARY. |
| VARBINARY | See VARBINARY. |
| BLOB | See BLOB. |
| DATE | See DATE. |
| TIME | See TIME. |
| TIMESTAMP |
If the source data type is a DATE, the timestamp is composed of the specified date and a time of 00:00:00. If the source data type is a TIME, the timestamp is composed of the CURRENT_DATE and the specified time. |
| DATALINK | See the rules for DataLink assignments in Assignments and comparisons. |
| ROWID | See ROWID. |
| XML | See the rules for XML assignments in Assignments and comparisons. |