Explicit Type Conversions

Go Up to Converting Data Types

When InterBase cannot do an implicit type conversion, you must perform an explicit type conversion using the CAST() function. Use CAST() to convert one data type to another inside a SELECT statement. Typically, CAST() is used in the WHERE clause to compare different data types. The syntax is:

CAST (value
| NULL AS datatype)

Use CAST() to translate the following data types:

• DATE, TIME, or TIMESTAMP data type into a CHARACTER data type.
• CHARACTER data type into a DATE, TIME, or TIMESTAMP data type.
• TIMESTAMP data type into a TIME or DATE data type.
• TIME or DATE data type into a TIMESTAMP data type.
• BOOLEAN into a CHAR or VARCHAR.
• BLOB subtype 1 into a VARCHAR.

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

… WHERE HIRE_DATE = (CAST(INTERVIEW_DATE AS DATE);

In the next example, CAST() is used to translate a DATE data type into a CHAR data type:

… WHERE CAST(HIRE_DATE AS CHAR) = INTERVIEW_DATE;

You can use CAST() to compare columns with different data types in the same table, or across tables. For more information, refer to “Working with Dates and Times” in the Embedded SQL Guide.

Converting a numeric data type to a character type requires a minimum length for the character type, as listed below.

Minimum character lengths for numeric conversions

Data type	Minimum length for converted character type
Decimal	20
Double	22
Float	13
Integer	11
Numeric	22
Smallint	6