You might run across another common problem with datetime data types. In this example, the order time matters. Your company has three shifts, with first-shift hours between 7:00 a.m. and 3:00 p.m., and you want to find all orders placed during the first shift from March 7 through March 11. The query
SELECT * FROM orders WHERE ord_dt >= 'March 7, 2001
7 am' AND ord_dt < 'March 11, 2001 3 pm'
isn't what you want because it returns data from all shifts between the beginning datetime and ending datetime. Alternatively, you could write a query that includes each day:
SELECT * FROM orders
WHERE ord_dt >= 'March 7, 2001 7 am' AND ord_dt
< 'March 7, 2001 3 pm'
OR ord_dt >= 'March 8, 2001 7 am' AND ord_dt
< 'March 8, 2001 3 pm'
OR ord_dt >= 'March 9, 2001 7 am' AND ord_dt
< 'March 9, 2001 3 pm'
...
This solution might be practical for a week's data, but for monthly or annual shift reporting, it would quickly become unwieldy. You could also try to pick the pieces out of the datetime fields, as follows:
SELECT * FROM orders
WHERE datepart(yyyy,ord_dt) = 2001
AND datepart(mm, ord_dt) = 3
AND datepart(dd, ord_dt) BETWEEN 7 AND 11
AND datepart(hh, ord_dt) BETWEEN 7 AND 14
The datepart query would return the correct answer, but it might cause performance problems. Avoid using any functions around the column names in a WHERE clause because using functions with a column in a WHERE clause prevents the column from being a search argument. The query optimizer might not be able to see the index statistics, so it would have to use default statistics values to determine whether a given index would be useful. Because SQL Server might not choose the fastest index to satisfy the query, let's keep looking for a better solution.
You could add a column to store the shift number, but sometimes shift hours change. I usually add another column to the table to store the time, which makes queries such as this one much simpler. Let's call the column ord_time and make it an integer. It will store the time of the order in the format hhmm (hours and minutes, using 24-hour time). The ord_dt column still has both the date and time, but this extra column will store the time only. (You can use a trigger to keep this denormalized column up-to-date.) Now your code can read
SELECT * FROM orders
WHERE (ord_dt >= 'March 7, 2001 7 am' AND ord_dt
< 'March 11, 2001 3 pm' )
AND (ord_time >= 0700 AND ord_time < 1500)
Early in a project's design phase, think about how reporting will be done. Often, people don't discover the datetime problem until they begin preparing the reports—at the end of the project, when they believe they're almost finished. So plan ahead for this eventuality.
Extra Use for Uniqueidentifier
Microsoft added the uniqueidentifier data type in SQL Server 7.0. You probably know that you can obtain uniqueidentifier values by using the NEWID() function. But you might not be aware that you can use a uniqueidentifier's column values to return records in a semirandom (quick and dirty) order. If you run the script that Listing 1 shows, the records come out sorted in uniqueidentifier column order, which means SQL Server returns them in the same order every time. This approach might be good enough for a one-time job, but it isn't random. If you need to return the records in a different order each time, see "Tips from the SQL Server MVPs" at http://www.sqlmag.com, InstantDoc ID 19842. Dejan Sarka shows a method to get (pseudo) random record returns even if a table doesn't have a uniqueidentifier column:
SELECT * FROM Authors ORDER BY NEWID()
Unlike when you use the script in Listing 1, the record order is different every time you use the NEWID() function directly in the ORDER BY clause.
Although these data type problems aren't earth-shattering, you can still waste time trying to figure out why things don't work as you expect. If you've discovered other data-type idiosyncrasies, forward them to me at wsnyder@ikon.com so that I can share them with others.