Basic Querying for DATETIMEs
For this section, I will use the Northwind sample database which is shipped with SQL Server.
Querying for DATETIME works pretty much the same way as querying for any other (numerical) data. That means that you will usually use comparison operators like =, <>, >= or <=. However, you can also use logical operators ,such as LIKE or BETWEEN. Let’s see this in action.
Example 1: You’re looking for an exact match with your criteria:
SELECT CustomerID , OrderDate FROM Orders WHERE OrderDate = ‘19960704’
CustomerID OrderDate
———- ——————————————————
VINET 1996-07-04 00:00:00.000
(1 row(s) affected)
Note, here’s the first and most important thing to watch out for when working with DATETIME. Since the current implementation contains not only a date portion, but also a time portion, you have to take this into account in your query. Fortunately, the Northwind Orders table only stores dates in the OrderDate column. But to illustrate my point here, we change this:
UPDATE Orders SET OrderDate = ‘19960704 12:31:00′ WHERE CustomerID=’VINET’ AND OrderDate = ‘19960704’
GO
When you now run the above query again, you get an empty rowset back.
CustomerID OrderDate
———- ——————————————————
(0 row(s) affected)
The only chance you have is to specify both the date and the time in your WHERE clause. However, in most cases, you don’t know the exact time and/or are only interested in the day and don’t care about the time.
Example 2: Searching for a specific period of time
Given the above situation you decide that it’s okay to see all rows with an OrderDate of ‘19960704’. But the above query won’t yield that result. What to do now? Well, here you have more than one method you can choose from.
SELECT CustomerID , OrderDate FROM Orders WHERE OrderDate >= ‘19960704’ AND OrderDate < ‘19960705’
CustomerID OrderDate
———- ——————————————————
VINET 1996-07-04 12:31:00.000
(1 row(s) affected)
This is probably the easiest method to deal with such queries. Basically, you specify that you want to retrieve all rows with a value equal or greater than ‘19960704’ (starting at midnight 00:00:00.000 that day), and smaller than ‘19960705’ (ending at midnight 00:00:00.000 the other day). That way you don’t need to worry about any time fraction in your data anymore. You might think, the above query can also be rewritten to:
SELECT CustomerID , OrderDate FROM Orders WHERE OrderDate BETWEEN ‘19960704’ AND ‘19960705’
CustomerID OrderDate
———- ——————————————————
VINET 1996-07-04 12:31:00.000 TOMSP 1996-07-05 00:00:00.000
(2 row(s) affected)
Caution! As you can see, this might prove to be a pitfall since BETWEEN is meant to be inclusive with the second date criteria in the search. One might question if this is intuitive, but that’s the way BETWEEN works. Even a rewrite to:
SELECT CustomerID , OrderDate FROM Orders WHERE OrderDate BETWEEN ‘19960704’ AND ‘19960704’
CustomerID OrderDate
———- ——————————————————
(0 row(s) affected)
doesn’t work. Remember that SQL Server assumes a time of midnight when you don’t explicitly request something else. So, the above query cannot find any row with a time unequal to midnight. When you use the first query to solve this kind of problems you’re on the safe side. Be explicit in what you request!
Btw, when you compare the execution plans of these queries, you’ll see that SQL Server internally resolves the BETWEEN operator to a comparison of >= and <= anyway. So, there’s really no valid point in using BETWEEN anyway, IMHO. Here’s an excerpt from the plan for the above query:
…SEEK:([Orders].[OrderDate] >= Convert([@1]) AND [Orders].[OrderDate] <= Convert([@2]))…
In the previous examples, you have seen the use of >= and <= in action, so I won’t need to give an example showing how to use these comparison operators. I’d rather like to focus on another logical operator you can use with DATETIMEs: LIKE. BOL states that you can search for a partial date or time value using the LIKE operator. So, basically something like this:
SELECT CustomerID , OrderDate FROM Orders WHERE OrderDate LIKE ‘%1996%’
CustomerID OrderDate
———- ——————————————————
VINET 1996-07-04 12:31:00.000
TOMSP 1996-07-05 00:00:00.000
HANAR 1996-07-08 00:00:00.000 …
In this example, we’re searching for all the rows with a partial date value of 1996 in the OrderDate column. So far, so good. Now, we extend this, since we now only want to see all rows in the month 07. One might think that we only need to write ‘%1996-07%’ in the WHERE clause. Running this query, you’ll get no resultset back. No row is found. To shorten things up here a bit, I would suggest not to bother using LIKE with DATETIME. SQL Server provides much better built-in functionalities when you need to deal with partial date or time values. The above query can easily be rewritten to:
SELECT CustomerID , OrderDate FROM Orders WHERE DATEPART(yyyy,OrderDate)=1996
CustomerID OrderDate
———- ——————————————————
VINET 1996-07-04 12:31:00.000
TOMSP 1996-07-05 00:00:00.000
HANAR 1996-07-08 00:00:00.000 …
With the use of the DATEPART function, it is also easy to query for a specific month.
SELECT CustomerID , OrderDate FROM Orders WHERE DATEPART(yyyy,OrderDate)=1996 AND DATEPART(mm,OrderDate)=7
CustomerID OrderDate
———- ——————————————————
VINET 1996-07-04 12:31:00.000
TOMSP 1996-07-05 00:00:00.000
HANAR 1996-07-08 00:00:00.000 …
In the latter case you would, btw, rather choose to use the technique provided in Example 2 and do:
SELECT CustomerID , OrderDate FROM Orders WHERE OrderDate >= ‘19960701’ AND OrderDate < ‘19960801’