How To Receive Data from a Single Table, concluded
(Page 1 of 5 )
If you have ever wondered how to code SELECT statements, this article is for you. It is the third of three parts, excerpted from chapter three of the book
Murach's SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162).
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, 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 3-13. How to use the BETWEEN operator
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.
Next: How to use the LIKE operator >>
More MS SQL Server Articles
More By Murach Publishing
|
 This article is excerpted from chapter three of the book Murach's SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 11 
