BETWEEN Condition

Use the BETWEEN condition to test whether the value of a numeric, character, or time expression is within a specified range.

(1)
BETWEEN Condition

1   %Expression1?  NOT BETWEEN  %Expression1 AND  %Expression1
Notes:

Usage

NULL values cannot satisfy the condition. Neither of the expressions that define the range can evaluate to NULL.

The three expressions in a BETWEEN condition must satisfy these restrictions:
  • All three expressions must evaluate to mutually compatible numeric, time, or character data types.
  • The value of the expression that immediately follows the BETWEEN keyword must be less than the value of the expression that follows the AND keyword.

Numeric and time expressions in BETWEEN conditions

For number expressions, less than means to the left on the real line.

For DATE and DATETIME expressions, less than means earlier in time.

For INTERVAL expressions, less than means a shorter span of time.

Character expressions in BETWEEN conditions

For CHAR, VARCHAR, and LVARCHAR expressions, less than means before in code-set order.

For NCHAR and NVARCHAR expressions, less than means before in the localized collation order, if one exists; otherwise, less than means before in code-set order.

Locale-based collation order, if one is defined for the locale, is used for NCHAR and NVARCHAR expressions. So for NCHAR and NVARCHAR expressions, less than means before in the locale-based collation order. For more information on locale-based collation order and the NCHAR and NVARCHAR data types, see the HCL OneDB™ GLS User's Guide.

For information on how relational operator expressions with NCHAR and NVARCHAR operands in databases that have the NLCASE INSENSITIVE property differ from their behavior in databases that are case sensitive, see the topic NCHAR and NVARCHAR expressions in case-insensitive databases.

The NOT keyword in BETWEEN conditions

For a BETWEEN condition to be TRUE depends on whether you include the NOT keyword.
  • If you omit the NOT keyword, the BETWEEN condition is TRUE only if the value of the expression on the left of the BETWEEN keyword is in the inclusive range of the values of the two expressions on the right of the BETWEEN keyword.
  • If the NOT keyword immediately precedes the BETWEEN keyword, the BETWEEN condition is TRUE only if the value of the expression on the left of the BETWEEN keyword is not in the inclusive range of the values of the two expressions on the right of the BETWEEN keyword.
Otherwise, the BETWEEN condition is FALSE.

Examples of BETWEEN conditions

The following examples illustrate BETWEEN conditions: 
order_date BETWEEN '6/1/97' and '9/7/97'

zipcode NOT BETWEEN '94100' and '94199'

EXTEND(call_dtime, DAY TO DAY) BETWEEN 
   (CURRENT - INTERVAL(7) DAY TO DAY) AND CURRENT

lead_time BETWEEN INTERVAL (1) DAY TO DAY 
   AND INTERVAL (4) DAY TO DAY

unit_price BETWEEN loprice AND hiprice