Converting Data Types
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
INTEGERfor 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, orTIMESTAMPdata type into aCHARACTERdata type.CHARACTERdata type into aDATE,TIME, orTIMESTAMPdata type.TIMESTAMPdata type into aTIMEorDATEdata type.TIMEorDATEdata type into aTIMESTAMPdata type.BOOLEANinto aCHARorVARCHAR.- 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.
| Data type | Minimum length for converted character type |
|---|---|
|
Decimal |
20 |
|
Double |
22 |
|
Float |
13 |
|
Integer |
11 |
|
Numeric |
22 |
|
Smallint |
6 |