Converting Data Types - Firebird

Normally, you must use compatible data types to perform arithmetic operations or to compare data in search conditions. If you need to perform operations on mixed data types, or if your programming language uses a data type that is not supported by Firebird, then data type conversions must be performed before the database operation can proceed.

Implicit Type Conversion

Dialects 1 and 3 behave differently with regard to implicit type conversion. This will be an issue if you wish to convert an existing database to dialect 3 and update its supporting applications.

  • In dialect 1, for some expressions, Firebird automatically converts the data to an equivalent data type (an implicit type conversion). The CAST() function can also be used, although it is optional in most cases.
  • In dialect 3, the CAST() function is required in search conditions to explicitly translate one data type into another for comparison purposes.

For example, comparing a DATE or TIMESTAMP column to '12/31/2003' in dialect 1 causes the string literal '12/31/2003' to be converted implicitly to a DATE entity.

SELECT * FROM TABLE_A WHERE START_DATE < '12/31/2003';

In dialect 3, using the explicit cast:

SELECT * FROM TABLE_A WHERE START_DATE < CAST('12/31/2003' AS DATE);

An expression mixing integers with string digits in dialect 1 implicitly converts the string to an integer if it can. In the following operation

3 + '1'

Dialect 1 automatically converts the character “1” to a SmallInt for the addition, whereas Firebird dialect 3 returns an error. It requires an explicit type conversion:

3 + CAST('1' AS SMALLINT)

Both dialects will return an error on the next statement, because Firebird cannot convert the character “a” to an integer:

3 + 'a'

Explicit Type Conversion: CAST()

When Firebird 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 in the WHERE clause, to compare different data types. The syntax is

CAST (value | NULL AS data type)

You can use CAST() to compare columns with different data types in the same table or across tables. For example, you can cast between properly formed strings and date/time types, and between various number types. For detailed information about casting between specific types, refer to the chapter in this part of the guide that deals with the data types in question.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics