BETWEEN

The BETWEEN expression determines whether an attribute's value falls within a range of values.

BETWEEN is useful in conjunction with WHERE clauses.

The syntax for BETWEEN is:

<attribute> BETWEEN <startValue> AND <endValue>

where <attribute> is the single-assign attribute whose value will be tested.

BETWEEN is inclusive, which means that it returns TRUE if the value of <attribute> is greater than or equal to the value of <startValue> and less than or equal to the value of <endValue>.

With one exception, <attribute> must be of the same data type as <startValue> and <endValue> (supported data types are integer, double, dateTime, duration, time, string, and Boolean). The exception is that you can use a mix of integer and double, because the integer is promoted to a double.

Note that if any of the BETWEEN arguments (<attribute>, <startValue>, or <endValue>) are NaN (Not a Number) values, then the expression evaluates to FALSE.

The following is a simple example of BETWEEN:

RETURN Results AS
SELECT SUM(AMOUNT_SOLD) AS SalesTotal
FROM SaleState
WHERE AMOUNT_SOLD BETWEEN 10 AND 100
GROUP BY CUST_STATE_PROVINCE