SQL Reference

Casting Between Data Types

There are many occasions where a value with a given data type needs to be cast to a different data type or to the same data type with a different length, precision or scale. Data type promotion (as defined in Promotion of Data Types) is one example where the promotion of one data type to another data type requires that the value is cast to the new data type. A data type that can be cast to another data type is castable from the source data type to the target data type.

Casting between data types can be done explicitly using the CAST specification (see CAST Specifications) but may also occur implicitly during assignments involving a user-defined types (see User-defined Type Assignments). Also, when creating sourced user-defined functions (see CREATE FUNCTION), the data types of the parameters of the source function must be castable to the data types of the function that is being created.

The supported casts between built-in data types are shown in Table 6.

The following casts involving distinct types are supported:

• cast from distinct type DT to its source data type S
• cast from the source data type S of distinct type DT to distinct type DT
• cast from distinct type DT to the same distinct type DT
• cast from a data type A to distinct type DT where A is promotable to the source data type S of distinct type DT (see Promotion of Data Types)
• cast from an INTEGER to distinct type DT with a source data type SMALLINT
• cast from a DOUBLE to distinct type DT with a source data type REAL
• cast from a VARCHAR to distinct type DT with a source data type CHAR
• cast from a VARGRAPHIC to distinct type DT with a source data type GRAPHIC.

It is not possible to cast a structured type value to something else. A structured type ST should not need to be cast to one of its supertypes, since all methods on the supertypes of ST are applicable to ST. If the desired operation is only applicable to a subtype of ST, then use the subtype-treatment expression to treat ST as one of its subtypes. See Subtype Treatment for details.

When a user-defined data type involved in a cast is not qualified by a schema name, the SQL path is used to find the first schema that includes the user-defined data type by that name. The SQL path is described further in CURRENT PATH.

The following casts involving reference types are supported:

• cast from reference type RT to its representation data type S
• cast from the representation data type S of reference type RT to reference type RT
• cast from reference type RT with target type T to a reference type RS with target type S where S is a supertype of T.
• cast from a data type A to reference type RT where A is promotable to the representation data type S of reference type RT (see Promotion of Data Types).

When the target type of a reference data type involved in a cast is not qualified by a schema name, the SQL path is used to find the first schema that includes the user-defined data type by that name. The SQL path is described further in CURRENT PATH.

Table 6. Supported Casts between Built-in Data Types

Target Data Type >             Source Data Type V	S M A L L I N T	I N T E G E R	B I G I N T	D E C I M A L	R E A L	D O U B L E	C H A R	V A R C H A R	L O N G V A R C H A R	C L O B	G R A P H I C	V A R G R A P H I C	L O N G V A R G	D B C L O B	D A T E	T I M E	T I M E S T A M P	B L O B
SMALLINT	Y	Y	Y	Y	Y	Y	Y	-	-	-	-	-	-	-	-	-	-	-
INTEGER	Y	Y	Y	Y	Y	Y	Y	-	-	-	-	-	-	-	-	-	-	-
BIGINT	Y	Y	Y	Y	Y	Y	Y	-	-	-	-	-	-	-	-	-	-	-
DECIMAL	Y	Y	Y	Y	Y	Y	Y	-	-	-	-	-	-	-	-	-	-	-
REAL	Y	Y	Y	Y	Y	Y	-	-	-	-	-	-	-	-	-	-	-	-
DOUBLE	Y	Y	Y	Y	Y	Y	-	-	-	-	-	-	-	-	-	-	-	-
CHAR	Y	Y	Y	Y	-	-	Y	Y	Y	Y	-	Y	-	-	Y	Y	Y	Y
VARCHAR	Y	Y	Y	Y	-	-	Y	Y	Y	Y	-	Y	-	-	Y	Y	Y	Y
LONG VARCHAR	-	-	-	-	-	-	Y	Y	Y	Y	-	-	-	-	-	-	-	Y
CLOB	-	-	-	-	-	-	Y	Y	Y	Y	-	-	-	-	-	-	-	Y
GRAPHIC	-	-	-	-	-	-	-	-	-	-	Y	Y	Y	Y	-	-	-	Y
VARGRAPHIC	-	-	-	-	-	-	-	-	-	-	Y	Y	Y	Y	-	-	-	Y
LONG VARG	-	-	-	-	-	-	-	-	-	-	Y	Y	Y	Y	-	-	-	Y
DBCLOB	-	-	-	-	-	-	-	-	-	-	Y	Y	Y	Y	-	-	-	Y
DATE	-	-	-	-	-	-	Y	Y	-	-	-	-	-	-	Y	-	-	-
TIME	-	-	-	-	-	-	Y	Y	-	-	-	-	-	-	-	Y	-	-
TIMESTAMP	-	-	-	-	-	-	Y	Y	-	-	-	-	-	-	Y	Y	Y	-
BLOB	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	Y
Notes See the description preceding the table for information on supported casts involving user-defined types and reference types. Only a DATALINK type can be cast to a DATALINK type. It is not possible to cast a structured type value to anything else.