Casting data types

Sometimes you need to cast or change the type of an expression to a different data type or to the same data type with a different length, precision, or scale.

For example, if you want to compare two columns of different types, such as a user-defined type based on a character and an integer, you can change the character to an integer or the integer to a character to make the comparison possible. A data type that can be changed to another data type is castable from the source data type to the target data type.

You can use cast functions or CAST specification to explicitly cast a data type to another data type. For example, if you have a column of dates (BIRTHDATE) defined as DATE and want to cast the column data type to CHARACTER with a fixed length of 10, enter the following:

SELECT CHAR (BIRTHDATE,USA)
   FROM CORPDATA.EMPLOYEE

You can also use the CAST specification to cast data types directly:

SELECT CAST(BIRTHDATE AS CHAR(10))
   FROM CORPDATA.EMPLOYEE