Range Operators

Depending on your database, may include BETWEEN and NOT BETWEEN. Use these operators when you want to ensure that your data is within a range of values.

Table 48. Examples

Example	Description
S.ORDERS.ORDER_DATE BETWEEN sysdate-7 AND sysdate	Retrieves all orders placed in the last seven days.
S.ORDERS.ORDER_DATE NOT BETWEEN '31-Jul-98' AND '01-Sep-98”	Retrieves all orders except those placed in August of 98

When doing comparisons on date columns, you may have to use a string-to-date function. Check your database documentation for the syntax of this function. For example, the MSSQL Server database uses the following syntax:

HIREDATE BETWEEN to_date('01-Jan-1990') AND to_date('01-Jan-1995') returns all rows where the HIREDATE column is between 1990 and 1995.

Note:

To build complex where clauses such as the above example, you must use the Add Custom button from the Where Clause Builder screen to custom build your clause.

Note:

Use of system functions is limited to custom clauses only. They may not be used in the Where Clause Wizard.