Chapter 3: How to retrieve data from a single table

In this chapter, you’ll learn how to code SELECT statements that retrieve data from a single table.

How to use the BETWEEN operator

Figure 3-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, 2008 and May 31, 2008. 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.

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 '2008-05-01' AND '2008-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.

Warning about date comparisons

• All columns that have the datetime data type include both a date and time, and so does the value returned by the GetDate function. But when you code a date literal like '2008-05-01', the time defaults to 00:00:00 on a 24-hour clock, or 12:00 AM (midnight). As a result, a date comparison may not yield the results you expect. For instance, May 31, 2008 at 2:00 PM isn't between '2008-05-01' and '2008-31-01'.
• To learn more about date comparisons, please see chapter 8.

---

Figure 3-13 How to use the BETWEEN operator