Using CAST( ) for Data Type Conversions

Go Up to Understanding SQL Expressions

Normally, only similar data types can be compared or evaluated in expressions. The CAST() function can be used in expressions to translate one data type into another for comparison purposes. The syntax for CAST() is:

CAST (<value> | NULL AS data_type)

For example, in the following WHERE clause, CAST() is used to translate a CHAR data type, INTERVIEW_DATE, to a DATE data type to compare against a DATE data type, HIRE_DATE:

WHERE HIRE_DATE = CAST(INTERVIEW_DATE AS DATE);

CAST() can be used to compare columns with different data types in the same table, or across tables. You can convert one data type to another as shown in the following table:

Compatible data types for CAST()

From  data type class	To data type class
Numeric	character, varying character, numeric
Character, varying character	numeric, date, time, timestamp
Date	character, varying character, timestamp
Time	character, varying character, timestamp
Timestamp	character, varying character, date, time
Blob, arrays	--
Boolean	character, varying character

An error results if a given data type cannot be converted into the data type specified in CAST().

When you cast a non-integer numeric data type to an integer data type, CAST() behaves like Delphi’s ROUND(x), where x is rounded to the nearest whole number; if x is exactly halfway between two whole numbers, the result is the number with the greatest absolute magnitude. For example:

CAST(1.6 as INTEGER) = 2
CAST(-1.5 as INTEGER) = -2
CAST(-1.6 as INTEGER) = -2

Advance To:

• Using UPPER() on Text Data