Converting Data Types

From InterBase
Jump to: navigation, search

Go Up to Specifying Data Types


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 InterBase, then data type conversions must be performed before the database operation can proceed. InterBase either automatically (dialect 1) converts the data to an equivalent data type (an implicit type conversion), or you can use the CAST() function (dialect 3) in search conditions to explicitly translate one data type into another for comparison purposes.

You cannot convert array data to any other data type, nor can any data type be converted to an array. Individual elements of an array, however, behave like members of the array’s base data type. To see how BLOBs can be converted to VARCHARS, see Using BLOBs with VARCHAR Data.


Implicit Type Conversions

InterBase supports several types of implicit type conversion. For example, comparing a DATE or TIMESTAMP column to ‘6/7/2000’ causes the string literal ‘6/7/2000’ to be converted implicitly to a DATE entity. An expression mixing integers with scaled numeric types or float types implicitly converts the integer to a like type.

However, InterBase dialect 3 differs from dialect 1 in this respect: in dialect 3, implicit string-to-integer conversion is not supported. For example, in the following operation:

3 + '1' = 4
  • InterBase dialect 1 automatically converts the character “1” to an INTEGER for the addition.
  • InterBase dialect 3 returns an error.

In dialect 3, an explicit type conversion is needed:

3 + CAST(‘1’ AS INT)

The next example returns an error in either dialect, because InterBase cannot convert the “a” to an INTEGER:

3 + 'a' = 4


Explicit Type Conversions

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 data_type)

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

Advance To: