Getting Correct Results from Date Comparisons

The discussion about string inequalities got us started in understanding date comparisons. In fact, I avoided addressing dates at all until you understood inequalities. That's because almost all date processing on SQL Server is done with inequalities, not equalities.

SQL Server doesn't really store dates. It stores datetime data, which is a composite data type made up of date information and time information. There are two data types for storing dates: datetime and smalldatetime. Both types consist of a date component and a time component.

In Transact-SQL, all dates are quoted strings formatted as dates and/or times. (We'll look at some date functions tomorrow in the section "Using Functions.") The output of dates includes both a date and time component, as in this query from the Employees table:

select LastName, HireDate
from Employees

Results:

LastName             HireDate
-------------------- ---------------------------
Davolio              1992-05-01 00:00:00.000
Fuller               1992-08-14 00:00:00.000
Leverling            1992-04-01 00:00:00.000
Peacock              1993-05-03 00:00:00.000
Buchanan             1993-10-17 00:00:00.000
Suyama               1993-10-17 00:00:00.000
King                 1994-01-02 00:00:00.000
Callahan             1994-03-05 00:00:00.000
Dodsworth            1994-11-15 00:00:00.000

As you can see, the hire dates are all entered as dates with a zero time (00:00:00.000), which is midnight. In all these cases, we can use an equality to find employees by hire date. Here's an example:

select LastName, HireDate
  from Employees
 where HireDate = 'Jan 2, 1994'

Results:

LastName             HireDate
-------------------- ---------------------------
King                 1994-01-02 00:00:00.000

You probably noticed the date format: 'Jan 2, 1994'. You can also use almost any date format. Here are some of the valid ways to present the same date, all to be enclosed in single quotes in the WHERE clause:

• 1/2/94

• 01/02/1994

• 1-2-94

• 1994-01-02

• 19940102

• January 2 1994

• 1.2.94

Dates in Other Languages and Formats

What about month names in other languages, or day-month-year ordering preferences? These are essentially localization issues that can be resolved by installing and selecting a different national language for your server or user profile.

A dateformat option in T-SQL changes the interpretation of date strings for your current session. To allow the database server to accept dates in day-month-year format, use this query:

set dateformat dmy

Every time you log in, you will need to set this setting. It's easier to set a national language for your login and use that to define your default dateformat.

The date output format reflects the choice of the developer of the SQL Query Analyzer tool itself. When you write your own applications, you will use your software development tools and report writers to improve the appearance of date information. T-SQL provides several functions that you can use with dates to change how dates are displayed as shown in the following example:

Select 	Convert(varchar,OrderDate,107)
From Orders

This SQL statement would format the date as shown:

Jul 04, 1996

You will be learning how to use these later in this book.

Experimenting with datetime Values

The Employees table doesn't have any nonzero times, so we need to fabricate a more lifelike situation. Let's create a temporary table and add some dates with nonzero times. This will enable us to experiment with real-life examples of datetime data to understand better how it works.

Listing 2.1 provides the script to create and populate a temporary table with some date values. (It's also on the CD-ROM if you don't want to type this much stuff.)

Code Listing 2.1. CHAP02_1—Create and Populate a Temporary Table to Test datetime Behavior

 1: create table #TempDate
 2: (
 3:     OrderNum int not null primary key,
 4:     OrderDate smalldatetime not null
 5: )
 6: go
 7: set nocount on
 8: insert #TempDate (OrderNum, OrderDate)
 9: values (1, '7/12/2000')
10: insert #TempDate (OrderNum, OrderDate)
11: values (2, '7/12/2000 5:00pm')
12: insert #TempDate (OrderNum, OrderDate)
13: values (3, '7/12/2000 5:15pm')
14: insert #TempDate (OrderNum, OrderDate)
15: values (4, '7/12/2000 11:59pm')
16: insert #TempDate (OrderNum, OrderDate)
17: values (5, '7/13/2000')
18: insert #TempDate (OrderNum, OrderDate)
19: values (6, '7/13/2000 7:15pm')
20: insert #TempDate (OrderNum, OrderDate)
21: values (7, getdate())
22: set nocount off
23: go
24: select OrderNum, OrderDate
25:   from #TempDate
26: go

You haven't formally learned how to create a temporary table or to insert data, but this code listing shows you how it's done. A temporary table is just what its name implies: a place to store data temporarily. The table is private to your session (so, if you open another SQL Query Analyzer window, you won't be able to see #TempDate there). The INSERT statement adds rows to the table. We'll learn more about INSERT on Day 7, "Adding, Changing, and Deleting Rows."

For now, just run the code listing as it's written and you should get this output:

OrderNum    OrderDate
----------- ---------------------------
          1 2000-07-12 00:00:00
          2 2000-07-12 17:00:00
          3 2000-07-12 17:15:00
          4 2000-07-12 23:59:00
          5 2000-07-13 00:00:00
          6 2000-07-13 19:15:00
          7 2000-06-07 18:16:00

The last row of data that was inserted in this table used the system function GetDate(). This means that the last row will always display the current date and time when the query was executed.

Generally, when you use date criteria, you are interested in retrieving data for a given time period: an hour, a day, a week, perhaps a year. The key in constructing all these queries is to learn to write date conditions that properly reflect the time period in which you are interested.

We want to see a list of orders in the #TempDate table from July 12, 2000. First, we'll try this query:

select OrderNum, OrderDate
  from #TempDate
 where OrderDate = '7/12/2000'

Results:

OrderNum    OrderDate
----------- ---------------------------
          1 2000-07-12 00:00:00

Even though there are four rows whose date value falls on the 12th of July, this query returns only one row. The other three rows are left behind. When you don't pass a time component in your datetime expression, the server assumes that you mean midnight, time zero. Only one order was entered on the stroke of midnight; the rest were entered at various other times during the day.

You saw the correct solution to this problem in your work with string inequalities. You might be tempted to use this query with BETWEEN:

/* Incorrect date searching method */
select OrderNum, OrderDate
  from #TempDate
 where OrderDate between '7/12/2000'and '7/13/2000'

Results:

OrderNum    OrderDate
----------- ---------------------------
          1 2000-07-12 00:00:00
          2 2000-07-12 17:00:00
          3 2000-07-12 17:15:00
          4 2000-07-12 23:59:00
          5 2000-07-13 00:00:00

The results of this query are incorrect. The last row in the set is from July 13, not July 12. The problem with using BETWEEN is seen in the closed-end comparison at the high end of the range.

The correct way to retrieve date range information is to use two inequalities joined with AND. Notice how, in this example, the second inequality leaves the high end of the range open (less than but not equal):

/* Preferred date searching method */
select OrderNum, OrderDate
  from #TempDate
 where OrderDate >= '7/12/2000'
   and OrderDate < '7/13/2000'

Results:

OrderNum    OrderDate
----------- ---------------------------
          1 2000-07-12 00:00:00
          2 2000-07-12 17:00:00
          3 2000-07-12 17:15:00
          4 2000-07-12 23:59:00

Searching for a Time

How do you search for a time? For example, I want a list of orders taken between 5 and 6 p.m. on any day. Will this query work?

/* Incorrect method of searching for a time */
select OrderNum, OrderDate
  from #TempDate
 where OrderDate >= '5:00pm'
   and OrderDate < '6:00pm'

Why didn't this query return any rows? Try inserting one more row into the #TempDate table, and then retrieve the new row:

insert #TempDate (OrderNum, OrderDate)
values (8, '4:00pm')
select OrderNum, OrderDate
from #TempDate
where OrderNum = 8

Results:

OrderNum    OrderDate
----------- ---------------------------
          8 1900-01-01 16:00:00

When you pass a time without a date, the server applies the system zero date, January 1, 1900, to the time. In the prior query, when we were looking for data between 5 p.m. and 6 p.m., the server was trying to find rows between 5 p.m. and 6 p.m. on January 1, 1900.

The only way to search a datetime column for a time value independent of the date is to use the datepart() function, which extracts a part of a datetime value. To find all orders between 5:00 p.m. and 6:00 p.m., you would use this query:

select OrderNum, OrderDate
  from #TempDate
 where datepart(hh, OrderDate) = 17

Results:

OrderNum    OrderDate
----------- ---------------------------
          2 2000-07-12 17:00:00
          3 2000-07-12 17:15:00

Although searching for dates is pretty straightforward, it's complicated to look for times. A new feature introduced in SQL Server 2000—computed fields—can make it easier. We'll learn more about datepart() and other date functions in the function discussion tomorrow.

It's considered good behavior to drop temporary tables when you are finished working with them. If you don't need to continue working with the #TempDate table, drop it by executing this statement:

drop table #TempDate.