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

Start of change You can use the TRY_CAST specification when there is a possibility that the cast could fail. For example, a character column might be expected to contain a valid integer value, but for some rows it does not. For rows where the cast is not successful, TRY_CAST returns the null value rather than an error. End of change

SELECT TRY_CAST(EMPNO AS INT)
   FROM CORPDATA.EMPLOYEE;