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

The SQL Server datetime datatype is accurate to the 333rd of a second -- that is, .003, .006, .009, and so on. That is why your .999 would round up to 0. Welcome to the ranks of developers (aka "all of us") messed up one time or another by this implementation.

Answer 2

The largest subsecond value that can be stored in datetime is .997.

So to use between it would need to be (for example)

between '2010-08-27 00:00:00.000' and '2010-08-27 23:59:59.997'

You should ideally use < rather than between so your code is compatible with the datetime2 data type where this assumption does not hold true.

Answer 3

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'

Answer 4

The solution you posted about unfortunately adds another new and subtle problem which will eventually come back and bite you: now you're skipping all the dates that are >= 23:59:59.003 and <= 23:59:59.997. I strongly suspect that you CAN subtract something smaller than 1 second, and that's 3 ticks, unless you are doing something that strips the extra time off. Be aware that smalldatetime won't even store seconds.

When dealing with boundary edges, it is never good to exploit the "known resolution" of the system representing the value to simulate an exclusive endpoint with an inclusive value. You found this out the hard way when converting between two representative systems that have different resolution. The best-practice way of going right up to the boundary but excluding it is with normal comparative operators.

So, as other posters have said, the correct answer is to use Dt >= @Dt1 AND Dt < @Dt2. I realize you have a billion stored procedures where this needs to be fixed, so may I suggest the following scheme to correct it:

1. Change your function to return the next day without any subtracted seconds or ticks.

2. Programmatically build "wrapper" SPs for all your stored procedures that use the original SP names. Rename the originals to something like SPName_NDC (non-date-compliant). Subtract 3 ms from each "to date" before passing it to the NDC version.

   Note: You can get SP parameter types and names from system tables. This might help you:

   SELECT
      ObjectSchema = Schema_Name(SO.schema_id),
      ObjectName = SO.name,
      ObjectType = SO.Type_Desc,
      Position = P.parameter_id,
      ParameterName = P.name,
      ParameterDataType = Type_name(P.user_type_id),
      P.max_length,
      P.[Precision],
      P.Scale,
      P.Is_Output,
      P.Has_Default_Value,
      P.Default_Value
   FROM
      sys.objects AS SO
      INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID
   WHERE
      SO.Type = 'P'
   

3. Now you can slowly fix each noncompliant SP over time. There shouldn't be a really noticeable performance hit to using the wrappers.

An example wrapper SP might look like this:

CREATE PROCEDURE dbo.ProfitReport
   @FromDate datetime,
   @ToDate datetime = NULL OUT
AS
SET @ToDate = DateAdd(ms, -3, @ToDate)
DECLARE @RC int
EXEC @RC = dbo.ProfitReport_NDC @FromDate, @ToDate OUT
RETURN @RC

You'll need to get a list of all date parameters and decide which ones represent end date boundaries. Be careful if your SPs have any XML or table-valued parameters.

You can be back in the world of sanity once again!

Note: if you upgrade to SQL 2008, before you can use the datetime2 data type you'll need to fix everything, anyway.