7.19.1 BETWEEN predicate

(1) Specification format

BETWEEN-predicate ::= value-expression-1 [NOT] BETWEEN value-expression-2 AND value-expression-3

To Page Top

(2) Explanation of specification format

value-expression-1:

Specifies the column to be evaluated by the BETWEEN predicate. It is specified in the form of a value expression. For details about value expressions, see 7.20 Value expression.

NOT:

If NOT is specified, values that do not satisfy the conditions specified following BETWEEN will become the target of retrieval.

BETWEEN value-expression-2 AND value-expression-3:

Specify a search range by specifying the lower and upper limits. These are specified in the form of value expressions.

Specify the lower limit of the search range in value-expression-2, and the upper limit of the search range in value-expression-3.

To Page Top

(3) Evaluation of the predicate

The BETWEEN predicate is TRUE for those rows that satisfy the following condition:

value-expression-2 <= value-expression-1 AND value-expression-1 <= value-expression-3

The following BETWEEN predicates are equivalent:

• value-expression-1 NOT BETWEEN value-expression-2 AND value-expression-3

• NOT (value-expression-1 BETWEEN value-expression-2 AND value-expression-3)

To Page Top

(4) Rules

1. For value-expression-1, you cannot specify a value expression that is composed solely of a dynamic parameter.

2. The data types that can be specified for value-expression-1, value-expression-2, and value-expression-3 are numeric data, character string data, and datetime data.

3. Specify value-expression-1, value-expression-2 and value-expression-3 such that the data types of the results of the three value expressions are all data types that can be compared. For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.

   However, if you specify the predefined input representation of a date, time, or time stamp for value-expression-1, you cannot specify datetime data for value-expression-2 and value-expression-3. For information about predefined input representations, see 6.3.3 Predefined character-string representations.

To Page Top

(5) Examples

Example 1

From the sales history table (SALESLIST), this example retrieves the customer ID (USERID), product code (PUR-CODE), and date of purchase (PUR-DATE) for customers who purchased products from September 4, 2011 to September 5, 2011.

SELECT "USERID","PUR-CODE","PUR-DATE"
  FROM "SALESLIST"
    WHERE "PUR-DATE" BETWEEN DATE'2011-09-04' AND DATE'2011-09-05'

The underlined portion indicates the BETWEEN predicate.

Example 2

From the sales history table (SALESLIST), this example retrieves the customer ID (USERID), product code (PUR-CODE), and date of purchase (PUR-DATE) for customers who purchased products on dates excluding September 4, 2011 to September 5, 2011.

SELECT "USERID","PUR-CODE","PUR-DATE"
  FROM "SALESLIST"
    WHERE "PUR-DATE" NOT BETWEEN DATE'2011-09-04' AND DATE'2011-09-05'

The underlined portion indicates the BETWEEN predicate.

To Page Top