Getting Bit by Datetime Rounding or Why 23:59:59.999 < '23:59:59.999'

Earlier today I was doing some ad-hoc querying to retrieve some numbers for the month of May. Not giving it deeper thought, I made a simple query like this:

SELECT
	SUM(SomeColumn)
FROM
	SomeTable
WHERE
	SomeDatetime BETWEEN '2011-05-01' AND '2011-05-31 23:59:59.999'

Much to my surprise, the last rows looked like this:

Why in the world are results from June included when I had an explicit predicate limiting the results to May? The answer can be found in one of my earlier posts on parsing dates. As SQL Server stores the millisecond part of a datetime with a precision of 1/300th of a second, with .997 being the highest possible stored value. .998 will be rounded down to .997 while .999 will be rounded up – causing a rollover of the day part.

Let’s setup a simple sample data set:

CREATE TABLE DateTest
(
	ID int,
	Created datetime
)

INSERT INTO
	DateTest (ID, Created)
VALUES 
	(1, '2011-05-31 23:59:59.996'),
	(2, '2011-05-31 23:59:59.997'),
	(3, '2011-05-31 23:59:59.998'),
	(4, '2011-05-31 23:59:59.999')

Performing my simple query reveals the same problem as earlier today:

SELECT
	*
FROM
	DateTest
WHERE
	Created BETWEEN '2011-05-01' AND '2011-05-31 23:59:59.999'

Row number 4 is inserted with a date of 2011-06-01 00:00:00.000 since the .999 millisecond part causes a day rollover. Equally, the .999 value causes the last predicate part to be interpreted as 2011-06-01 00:00:00.000 during the CONVERT_IMPLICIT conversion.

A simple rewrite of the query guarantees to return just the results we want:

SELECT
	*
FROM
	DateTest
WHERE
	Created >= '2011-05-01' AND Created < '2011-06-01'