Using CAST( ) for Data Type Conversions

From InterBase

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:


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


character, varying character, numeric

Character, varying character

numeric, date, time, timestamp


character, varying character, timestamp


character, varying character, timestamp


character, varying character, date, time

Blob, arrays



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: