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.

Table 1. 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 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.

Table 2. 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:

Table 3. 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

See TIMESTAMP, using the precision of the target data type as the second argument.

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.