When Datetime Data Types Behave Strangely

I'm having difficulty troubleshooting strange behavior of datetime values. SQL Server is incorrectly including rows in or excluding them from result sets when the date range is within a few milliseconds of my target threshold. Why is this happening?

A Few people know exactly how datetime values work in SQL Server. If you aren't one of the few, your T-SQL calculations based on datetime values could be wrong. Let's use an imaginary table called OrderDateTest to explore datetime behavior. Listing 1 shows a script that builds and populates OrderDateTest with some test records.

Let's say that OrderDateTest is the central order-processing table for an e-commerce system. You offer commissions to the sales team based on when an order was processed, so you need to carefully track order time. Suppose the company offers a sales bonus for orders processed on March 31, 2003. Your job is to sum up the value of the OrderDateTest table's OrderAmount column for that date.

SQL Server doesn't provide separate data types for date and time. Instead, you have to use the database's composite datetime data type, which places both date and time in the same column. Many T-SQL developers would use a query like the following to sum the OrderAmount column.

SELECT SUM(OrderAmount)FROM OrderDateTestWHERE OrderDate >= '03/31/03' AND OrderDate < '04/01/03'

This query looks safe. You don't care about the time of the order, just the date, so you ask for rows that are greater than or equal to 03/31/03 and less than 04/01/03. SQL Server has to add a time component to those values, which defaults to 00:00:00:000. So the logical range becomes >= '03/31/03 00:00:00:000' AND < '04/01/03 00:00:00:000'. That should give you all rows with an OrderDate that occurred on 03/31/03, right? Unfortunately, no.

Run the two INSERT statements and the SELECT statement that Listing 2 shows to see the danger of working with date ranges in SQL Server. Figure 1 shows the result set.

What happened? The row with OrderId = 5 has a datetime value of 2003-03-31 00:00:00.000, but we added 2003-03-30 59:59:59.999. The row with OrderId = 6 doesn't show up in the result set even though it has a datetime value of 2003-03-31 59:59:59.999, which is clearly a date from 3/31/03. Row 6 should be in the result set, and row 5 shouldn't be. Instead, you see the opposite. SQL Server appears to have changed the datetime values provided for the row with OrderId = 5.

You can find the answer to this strange behavior in the SQL Server Books Online (BOL) topic "Datetime and Smalldatetime." This BOL entry says that the datetime data type has an "accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in our OrderDateTest table." Table 1 shows examples of this rounding process.

With this information, let's look at Figure 1 again. SQL Server has rounded up the datetime value of 2003-03-30 23:59:59.999 for OrderId = 5 to 2003-03-31 00:00:00.000, placing it into the date range. The system also rounded up the datetime value for OrderId = 6; the new value is 2003-04-01 00:00:00.000, placing it out of the date range. Be careful when you're handling dates in SQL Server. Offering specific advice to solve every datetime processing conundrum is impossible. Just make sure that you understand how SQL Server handles date value comparisons and that your code is prepared to deal with the behavior.