ASA SQL Reference
  SQL Data Types
    Data type conversions

Conversion when using comparison operators

When a comparison (such as =) is performed between arguments with different data types, one or both arguments must be converted so that the comparison is done using one data type. Sometimes it is preferable for you to explicitly convert the argument.

Adaptive Server Anywhere uses the following rules to perform a comparison:

1. If the data types of the arguments have a common super type, convert to the common super type and compare. The super types are the final data type in each of the following lists:

   • BIT > TINYINT > UNSIGNED SMALLINT > UNSIGNED INTEGER > UNSIGNED BIGINT > NUMERIC

   • SMALLINT > INTEGER > BIGINT > NUMERIC

   • REAL > DOUBLE

   • CHAR > LONG VARCHAR

   • BINARY > LONG BINARY

   • DATE > TIMESTAMP

   • TIME > TIMESTAMP

   For example, if the two arguments are of types BIT and TINYINT, they are converted to NUMERIC.

2. If Rule 1 does not apply, and either data type has the type DATE or TIMESTAMP, convert to TIMESTAMP and compare.

   For example, if the two arguments are of type REAL and DATE, they are both converted to TIMESTAMP.

3. If Rules 1 and 2 do not apply, and one argument has CHARACTER data type and the other has BINARY data type, convert to BINARY and compare.

4. If Rules 1 to 3 do not apply, and one argument has NUMERIC data type and the other has FLOAT, convert to DOUBLE and compare.

5. If none of the rules apply, convert to NUMERIC and compare.

   For example, if the two arguments have REAL and CHAR data types, they are both converted to NUMERIC.

Notes 

• You can override these rules by explicitly casting arguments to another type. For example, if you want to compare a DATE and a CHAR as a CHAR, then you need to explicitly cast the DATE to a CHAR.

• Rules 2 and 5 may lead to conversions that fail.