Mark S. Rasmussen improve.dk
Jun 16
2011

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:

image_2

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'
image_4

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'
image_10
Mark S. Rasmussen
I'm the CTO at iPaper where I cuddle with databases, mold code and maintain the overall technical & team responsibility. I'm an avid speaker at user groups & conferences. I love life, motorcycles, photography and all things technical. Say hi on Twitter, write me an email or look me up on LinkedIn.
Comments

Leave a Comment