BETWEEN predicate

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-expression--+-----+--BETWEEN--expression--AND--expression---><
               '-NOT-'
 

The BETWEEN predicate compares a value with a range of values.

If the operands of the predicate are SBCS data, mixed data, or Unicode data, and if the sort sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the sort sequence.

The BETWEEN predicate:

   value1 BETWEEN value2 AND value3

is logically equivalent to the search condition:

   value1 >= value2 AND value1 <= value3

The BETWEEN predicate:

   value1 NOT BETWEEN value2 AND value3

is equivalent to the search condition:

   NOT(value1 BETWEEN value2 AND value3);that is,
   value1 < value2 OR value1 > value3.

If the operands of the BETWEEN predicate are strings with different CCSIDs, operands are converted as if the above logically-equivalent search conditions were specified.

Given a mixture of datetime values and string representations of datetime values, all values are converted to the data type of the datetime operand.

Examples

            EMPLOYEE.SALARY BETWEEN 20000 AND 40000
 
            SALARY NOT BETWEEN 20000 + :HV1 AND 40000