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 is one example where the promotion of one data type to another data type requires that the value be cast to the 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.

Start of changeThe casting of one data type to another can occur implicitly or explicitly. The cast functions, CAST specification, or XMLCAST specification can be used to explicitly change a data type, depending on the data types involved. In addition, when a sourced user-defined function is created, the data types of the parameters of the source function must be castable to the data types of the function that is being created.End of change

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 casting to a binary string, an error is returned.

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 casting a parameter marker or NULL value to the XML data type, the CAST specification can be used. XML input can also be specified for the CAST specification when the result data type is XML.

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 data 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

When a distinct type is involved in a cast, a cast function that was generated when the distinct type was created is used. How DB2 chooses the function depends on whether function notation or CAST specification syntax is used. (For details, see Function resolution and CAST specification, respectively.) Function resolution is similar for both. However, in CAST specification, when an unqualified distinct type is specified as the target data type, DB2 first resolves the schema name of the distinct type and then uses that schema name to locate the cast function.

For casts between built-in data types, the following table shows the supported casts.

Table 2. Supported casts between built-in data types
Cast from data type – To data type1
SMALLINT INTEGER BIGINT DECIMAL DECFLOAT REAL DOUBLE CHAR VARCHAR CLOB GRAPHIC VARGRAPHIC DBCLOB BINARY VARBINARY BLOB DATE TIME Start of changeTIMESTAMP WITHOUT TIME ZONEEnd of change Start of changeTIMESTAMP WITH TIME ZONEEnd of change ROWID XML
SMALLINT Y Y Y Y Y Y Y Y Y                          
INTEGER Y Y Y Y Y Y Y Y Y                          
BIGINT Y Y Y Y Y Y Y Y Y                          
DECIMAL Y Y Y Y Y Y Y Y Y                          
DECFLOAT Y Y Y Y Y Y Y Y Y                          
REAL Y Y Y Y Y Y Y Y Y                          
DOUBLE Y Y Y Y Y Y Y Y Y                          
CHAR Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Start of changeYEnd of change Y  
VARCHAR Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Start of changeYEnd of change Y  
CLOB               Y Y Y Y Y Y Y Y Y            
GRAPHIC Y Y Y Y Y Y Y Y2 Y2 Y2 Y Y Y Y Y Y Y3 Y3 Y3 Start of changeY3End of change    
VARGRAPHIC Y Y Y Y Y Y Y Y2 Y2 Y2 Y Y Y Y Y Y Y Y Y Start of changeY3End of change    
DBCLOB               Y2 Y2 Y2 Y Y Y Y Y Y            
BINARY                           Y Y Y            
VARBINARY                           Y Y Y            
BLOB                           Y Y Y            
DATE               Y Y               Y          
TIME               Y Y                 Y        
Start of changeTIMESTAMP WITHOUT TIME ZONEEnd of change               Y Y               Y Y Y Start of changeYEnd of change    
Start of changeTIMESTAMP WITH TIME ZONEEnd of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of changeYEnd of change Start of changeYEnd of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of change End of change Start of changeYEnd of change Start of changeYEnd of change Start of changeYEnd of change Start of changeYEnd of change Start of change End of change Start of change End of change
ROWID               Y Y         Y Y Y         Y  
XML                                           Y
Note:
  1. Other synonyms for the listed data types are considered to be the same as the synonym listed. Some exceptions exist when the cast involves character string data if the subtype is FOR BIT DATA.
  2. The result length for these casts is 3 * LENGTH(graphic string).
  3. These data types are castable between each other only if the data is Unicode.
Table 3 shows where to find information about the rules that apply when casting to the identified target data types.
Table 3. Rules for casting to a data type
Target data type Rules
SMALLINT SMALLINT
INTEGER INTEGER or INT
BIGINT BIGINT
DECIMAL DECIMAL or DEC
NUMERIC DECIMAL or DEC
REAL REAL
DOUBLE DOUBLE_PRECISION or DOUBLE
DECFLOAT DECFLOAT
CHAR CHAR
VARCHAR VARCHAR
CLOB CLOB
GRAPHIC GRAPHIC
VARGRAPHIC VARGRAPHIC
DBCLOB DBCLOB
BINARY BINARY
VARBINARY VARBINARY
BLOB BLOB
DATE DATE
TIME TIME
Start of changeTIMESTAMP WITHOUT TIME ZONEEnd of change Start of changeIf the source data type is a character or graphic string, see TIMESTAMP, where one operand is specified. If the string contains a time zone, an error is returned.

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 is a TIMESTAMP WITH TIME ZONE, the resulting timestamp is the timestamp without time zone element of the specified datetime value, which is the local timestamp in the corresponding time zone. For example: cast('2008-04-12-07.30.00.0-6:00' as TIMESTAMP) returns 2008-04-12-07.30.00.0.

If the source type is a TIMESTAMP WITHOUT TIME ZONE the timestamp is the specified value.

End of change
Start of changeTIMESTAMP WITH TIME ZONEEnd of change Start of changeIf the source data type is a character or graphic string or TIMESTAMP WITHOUT TIME ZONE, see TIMESTAMP_TZ, where one operand is specified. If the string contains a time zone, an error is returned.

If the source type is a TIMESTAMP WITH TIME ZONE, the timestamp is the specified value.

End of change
ROWID ROWID
Start of change
Table 4. The derived length of an argument when a built-in scalar function is invoked and implicit casting is required.
  Target data type
Source data type CHAR GRAPHIC VARCHAR
VAR-
GRAPHIC
 CLOB DBCLOB BLOB
TIME
STAMP
(precision)
 DECFLOAT
SMALLINT 6 6 6 6          
INTEGER 11 11 11 11          
BIGINT 20 20 20 20          
DECIMAL
(p,s)
2+p 2+p 2+p 2+p          
REAL 24 24 24 24          
DOUBLE 24 24 24 24          
DECFLOAT 42 42 42 42          
CHAR(n)               12 34
VARCHAR
(n)
min(n,254)             12 34
CLOB(n)                  
GRAPHIC
(n)
               12 34
VARGRAPHIC
(n)
               12 34
DBCLOB
(n)
                 
BLOB(n)                  
TIME 8 8 8 8          
DATE 10 10 10 10          
TIME-
STAMP(p)
WITHOUT
TIME
ZONE
 If p=0 then 19, otherwise 20+p If p=0 then 19, otherwise 20+p If p=0 then 19, otherwise 20+p If p=0 then 19, otherwise 20+p          
TIME-
STAMP(p)
WITH
TIME
ZONE
 If p=0 then 148, otherwise 149+p If p=0 then 148, otherwise 149+p If p=0 then 148, otherwise 149+p If p=0 then 148, otherwise 149+p          
End of change

Casting non-XML values to XML values

Table 5. Supported Casts from Non-XML Values to XML Values
Source Data Type Target Data Type
XML Resulting XML Schema Type
SMALLINT Y xs:short
INTEGER Y xs:int
BIGINT Y xs:long
DECIMAL Y xs:decimal
DECFLOAT N  
REAL N  
FLOAT Y xs:double
DOUBLE Y xs:double
CHAR Y xs:string
VARCHAR Y xs:string
CLOB Y xs:string
GRAPHIC Y xs:string
VARGRAPHIC Y xs:string
DBCLOB Y xs:string
BINARY N  
VARBINARY N  
BLOB N  
character type FOR BIT DATA N  
DATE N  
TIME N  
Start of changeTIMESTAMP WITHOUT TIME ZONEEnd of change Start of changeNEnd of change Start of change End of change
Start of changeTIMESTAMP WITH TIME ZONEEnd of change Start of changeNEnd of change Start of change End of change
ROWID N  
distinct type N  

When character string values are cast to XML values, the resulting xs:string atomic value cannot contain illegal XML characters. If the input character string is not in Unicode, the input characters are converted to Unicode.

Casting XML values to non-XML values

An XMLCAST from an XML value to a non-XML value can be described as two casts: an XQuery cast that converts the source XML value to a target XQuery data type that corresponds to the SQL target type, followed by a cast from the corresponding XQuery data type to the actual SQL type. The target XQuery data type is an XML schema data type like xs:decimal or xs:string, as shown in the follow table.

An XMLCAST is supported if the target type has a corresponding XQuery target type that is supported, and if there is a supported XQuery cast from the type of the source value to the corresponding XQuery target type. The target type that is used in the XQuery cast is based on the corresponding XQuery target type and might contain some additional restrictions.

The following table lists the XQuery types that result from such conversion.

Table 6. Supported Casts from XML Values to Non-XML Values
Target Data Type Source Data Type
XML Corresponding XQuery Target Type
SMALLINT Y xs:integer
INTEGER Y xs:integer
BIGINT Y xs:integer
DECIMAL Y xs:decimal
DECFLOAT Y xs:double
REAL Y xs:double
FLOAT Y xs:double
DOUBLE Y xs:double
CHAR Y xs:string
VARCHAR Y xs:string
CLOB Y xs:string
GRAPHIC Y xs:string
VARGRAPHIC Y xs:string
DBCLOB Y xs:string
BINARY N  
VARBINARY N  
BLOB N  
character type FOR BIT DATA N  
DATE Y xs:date
TIME Y xs:time
TIMESTAMP Y xs:dateTime
ROWID N  
distinct type N  
The following restrictions are in effect when a value is cast from an XQuery target data type to a target SQL data type:
  • If the target type is one of the character or graphic string types, the resulting XML value is converted, if necessary, to the CCSID of the target data type using the rules described in Conversion rules for string assignment, before it is converted to the target type with a limited length. Truncation occurs if the specified length limit is smaller than the length of the resulting string after CCSID conversion. A warning occurs if any non-blank characters are truncated. If the target type is a fixed-length string type (CHAR or GRAPHIC) and the specified length of the target type is greater than the length of the resulting string from CCSID conversion, blanks are padded at the end. This truncation and padding behavior is similar to retrieval assignment of character or graphic strings.
  • If the target type is DOUBLE or REAL and the source XML value after the XQuery cast is an xs:double value of INF, -INF, or NaN, an error is returned. If the source value is an xs:double negative zero, the value is converted to positive zero. If the source value is beyond the range of the target data type, an overflow error is returned. If the source value contains more significant digits than the precision of the target data type, the source value is rounded to the precision of the target data type.
  • If the target type is DECFLOAT and the source XML value is an xs:double value of INF, -INF, or NaN, the result will be the corresponding special DECFLOAT values INF, -INF, or NaN. If the source value is an xs:double negative zero, the result is negative zero. If the target type is DECFLOAT(16) and the source value is beyond the range of DECFLOAT(16), an overflow error is returned. If the source value has more than 16 significant digits, the value is rounded according to the ROUNDING mode that is in effect. This rounding behavior is the same as what is used during the cast of DECFLOAT(34) to DECFLOAT(16).
  • If the target type is DECIMAL, the resulting xs:decimal value is converted, if necessary, to the precision and scale of the target data type. The necessary number of leading zeros is added or removed. In the fractional part of the number, the necessary number of trailing zeros is added or the necessary number of digits is eliminated. This truncation behavior is similar to the behavior of the cast from DECIMAL to DECIMAL.
  • Start of changeIf the target type is DATE, TIME, or TIMESTAMP WITHOUT TIME ZONE, the resulting XML value is adjusted to UTC time and the time zone component is removed. If the source does not include a time zone and the target data type is TIMESTAMP WITH TIME ZONE, zeroes are used for the time zone component. If the target type is TIME and the resulting XML value contains a seconds component with non-zero digits after the decimal point, those digits are truncated. If the target type is DATE or timestamp, the year part of the resulting xs:date or xs:dateTime value must be in the range of 0001 to 9999. If the target type is timestamp and the precision of the target timestamp is less than 12, the fractional seconds part of the xs:dateTime value will be truncated to the target timestamp precision.End of change