Assignment and comparison

The basic operations of SQL are assignment and comparison.

Assignment operations are performed during the execution of statements such as INSERT and UPDATE statements. In addition, when a function is invoked or a stored procedure is called, the arguments of the function or stored procedure are assigned. Comparison operations are performed during the execution of statements that include predicates and other language elements such as ORDER BY.

The basic rule for both operations is that data types of the operands must be compatible.

The following table shows the compatibility of data types for assignments and comparisons.

Table 1. Data type compatibility for assignments and comparisons
Operand BYTES SHORT INT LONG DOUBLE BIT CHAR PACKED ZONED DATE TIME FLOAT TIMESTAMP
BYTES Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes No
SHORT Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes No
INT Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes No
LONG Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes No
DOUBLE Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes No
BIT Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes No
CHAR Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
PACKED Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes No
ZONED Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes No
DATE No No No No No No Yes No No Yes Yes No Yes
TIME No No No No No No Yes No No Yes Yes No Yes
FLOAT Yes Yes Yes Yes Yes Yes Yes Yes Yes No No Yes No
TIMESTAMP No No No No No No Yes No No Yes Yes No Yes
Notes:
  1. LOBs and bit data are not supported.
  2. The compatibility of datetime values is limited to assignment and comparison:
    • Datetime values can be assigned to string columns and to string variables.
    • A valid string representation of a date can be assigned to a date column or compared to a date.
    • A valid string representation of a time can be assigned to a time column or compared to a time.
    • A valid string representation of a timestamp can be assigned to a timestamp column or compared to a timestamp.
  3. Character strings can be assigned to XML columns.