Inequality Operators and Dates

The inequality operators are especially useful for searching dates. When a date is inserted into a table, the complete value of the column includes the date plus the time. If no time is specified, a default time of midnight is used.

Consider the rows in the sales table shown in Table 3.3.

If you asked for all the sales made on November 19, your first attempt at a query might look something like this:

select    *
from      sales
where     saledate = ’11/19/96'

invoice_num     saledate      amount
-----------     --------      ------

(0    row(s)   affected)

Datetime columns always contain a value for the date and the time. Because no time was specified in your WHERE clause, the date in the WHERE clause expands to “11/19/96 12:00:00.000AM.” No sales were made at precisely midnight, so no rows are returned.

How can you overcome this? Use the inequality operators. To get all the sales made on November 19, use this query:

select     *
from       sales
where      saledate >= ’11/19/96'
           and saledate < ’11/20’96'

invoice_no     saledate               amount
1              Nov 19 1996 5:00PM     $135.16
2              Nov 19 1996 7:13PM     $19.61

(2    row(s)  affected)

In the pubs database, there are three tables with datetime columns. The sales and employee tables always use midnight as times, and the titles table uses midnight for all but two books. If the datetime column in a table always uses midnight for a date, use of inequality operators isn’t necessary when querying the table; searching for “11/19/96” will find the November 19 midnight values. Keep this in mind if you build a table that accepts datetime values.

Syntax for WHERE

Here is the syntax for the WHERE predicate as it applies to the binary operators in Table 3.1:

select    <column list>
from      <table list>
where    (<column | constant> <operator> <column | constant>) <AND | OR>
         (<column | constant> <operator> <column | constant>) (…)

Using BETWEEN

T-SQL includes the BETWEEN keyword to allow a search for a range of values. If I wanted to search through the employee table to find all the employees who were hired in the year 1991, I could use this next query:

select      fname, lname, hire_date
from        employee
where       hire_date between ’01/01/91' and ’01/01/92'
order by    hire_date

fname                lname                         hire_date
---------------      -----------------             -------------------
Lesley               Brown                         Feb 13 1991 12:00AM
Sven                 Ottlieb                       Apr 5 1991 12:00AM
Janine               Labrune                       May 26 1991 12:00AM
Ann                  Devon                         Jul 16 1991 12:00AM
Roland               Mendel                        Sep 5 1991 12:00AM
Aria                 Cruz                          Oct 26 1991 12:00AM
Diego                Roel                          Dec 16 1991 12:00AM

(7 row(s) affected)

I also used an ORDER BY to order the result set. You learned all about ORDER BY yesterday. The WHERE and ORDER BY predicates can work together to first select a subset of rows (the WHERE) and then sort those rows (the ORDER BY). When you specify multiple predicates in a SELECT, the order is important: SELECT with its column list is always first, followed by the FROM list, and then the WHERE conditions, all of which always come before ORDER BY.

---

Technical Note:  When the server receives a query that uses BETWEEN, the optimizer (the part of the server that decides the best way to resolve your query) changes the BETWEEN into two WHERE expressions joined by an AND. For example, the query in the previous example becomes this:

select     fname, lname, hire_date
from       employee
where      hire_date >=’01/01/91'
           and hire_date <= ’01/01/92'
order by   hire_date

---