SQL Server DateTime parameter 'rounding' warning

Question

More of a warning than a question:

We resolved a very puzzling bug this morning. We have a variety of reports that allow users to enter date ranges they want to run. The assumption is, if you ask for a report from 8/1/2010 to 8/10/2010 you meant to include 8/10/2010 so the end-date of the report isn't 8/10, it's something after that.

It can't be 8/11/2010 becuase some of these reports rollup everything that happened during a day grouping them by that day which is at midnight, so a daily rollup would include an extra day - not what we wanted.

To avoid the possibility of missing any items very very close to the end of the day, we computed the end date as 'one tick' less than tomorrow:

public static DateTime EndOfDay(DateTime day)
{
    return day.Date.AddDays(1).AddTicks(-1);
}

Internally this ends up something like 8/10/2010 12:59:59.9999PM

Well, when you pass this DateTime to a DATETIME parameter in SQL Server it rounds the value UP to 8/11/2010 00:00:00! And since our query uses

DateField BETWEEN @FromDate AND @ToDate

instead of

DateField >= @FromDate AND DateField < @ToDate

We were seeing reports from 8/1/2010-8/10/2010 include items from 8/11/2010.

The only way we discovered the real problem was by round-tripping the dates thru a string. DateTime.ToString() rounds too so we'd end up with 8/1/2010 12:59:59PM which SQL Server was happy with.

So now our 'end of day' method looks like this:

public static DateTime EndOfDay(DateTime day)
{
    // Cant' subtract anything smaller (like a tick) because SQL Server rounds UP! Nice, eh?
    return day.Date.AddDays(1).AddSeconds(-1);
}

Sorry not a question - just thought someone might find it useful.

Answer 1

It's because of the accuracy of the DATETIME datatype, which has an accuracy (quote):

Rounded to increments of .000, .003, or .007 seconds

So yes you do have to be careful in certain situations (e.g. 23:59:59.999 will be rounded up to 00:00 of the following day, 23:59:59.998 will be rounded down to 23:59:59.997)

SELECT CAST('2010-08-27T23:59:59.997' AS DATETIME)
SELECT CAST('2010-08-27T23:59:59.998' AS DATETIME)
SELECT CAST('2010-08-27T23:59:59.999' AS DATETIME)

As of SQL Server 2008, there is a new DATETIME2 datatype which gives greater accuracy down to 100 nanoseconds.

When I'm doing queries on a DATETIME field which contains a time element, I don't use BETWEEN for this reason.

e.g. I prefer

WHERE DateField >= '2010-08-27' AND DateField < '2010-08-28'

instead of:

WHERE DateField BETWEEN '2010-08-27' AND '2010-08-27T23:59:59.997'