How to Retrieve Data from a Single Table - How to Use the BETWEEN Operator
(Page 13 of 17 )
Figure 13 shows how to use the BETWEEN operator in a WHERE clause. When you use this operator, the value of a test expression is compared to the range of values specified in the BETWEEN phrase. If the value falls within this range, the row is included in the query results.
The first example in this figure shows a simple WHERE clause that uses the BETWEEN operator. It retrieves invoices with invoice dates between May 1, 2002 and May 31, 2002. Note that the range is inclusive, so invoices with invoice dates of May 1 and May 31 are included in the results.
The second example shows how to use the NOT operator to select rows that are not within a given range. In this case, vendors with zip codes that aren't between 93600 and 93799 are included in the results.
The third example shows how you can use a calculated value in the test expression. Here, the PaymentTotal and CreditTotal columns are subtracted from the InvoiceTotal column to give the balance due. Then, this value is compared to the range specified in the BETWEEN phrase.
The last example shows how you can use calculated values in the BETWEEN phrase. Here, the first value is the result of the GETDATE function, and the second value is the result of the GETDATE function plus 30 days. So the query results will include all those invoices that are due between the current date and 30 days from the current date.
Figure 13: The syntax of the WHERE clause with a BETWEEN phrase
WHERE test_expression [NOT] BETWEEN begin_expression AND end_expression
Examples of the BETWEEN phrase
A BETWEEN phrase with literal values
WHERE InvoiceDate BETWEEN '2002-05-01' AND '2002-05-31'
A BETWEEN phrase preceded by NOT
WHERE VendorZipCode NOT BETWEEN 93600 AND 93799
A BETWEEN phrase with a test expression coded as a calculated value
WHERE InvoiceTotal - PaymentTotal - CreditTotal BETWEEN 200 AND 500
A BETWEEN phrase with the upper and lower limits coded as calculated
values
WHERE InvoiceDueDate BETWEEN GetDate() AND GetDate() + 30
Description
- You can use the BETWEEN phrase to test whether an expression falls within a range of values. The lower limit must be coded as the first expression, and the upper limit must be coded as the second expression. Otherwise, the result set will be empty.
- The two expressions used in the BETWEEN phrase for the range of values are inclusive. That is, the result set will include values that are equal to the upper or lower limit.
- You can use the NOT operator to test for an expression that's not within the given range.
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Use the LIKE Operator >>
More MS SQL Server Articles
More By Bryan Syverson
