Subscribe to this blog
Briefcase
Print
There's one report that I look at every day to assess how things are going on a project we launched this year. The report is nothing fancy, if anything it's down right ugly, but it is very easy to modify when needed and lets us measure things and then see if the measure was working. If you've been around reports much you know that sooner or later you get to a point where the report doesn't seem correct. Then someone has to go down the rabbit hole to figure out who is wrong, you or the report! The more complex the report, the more often this happens, and it can take days to resolve in the worst cases.
On this particular report there's been one number that seemed like it was off by just a little. Actually, I know that particular number was solid, but the measure on the breakouts of that number weren't coming up quite right. Bothered me, but not enough to dig in because I knew the final total was good. Still, it's been nagging at me and as I looked at the numbers today, I decided to go check a sudden hunch - and it turned out to be right for once (though perhaps not the end of the story).
For this report I pass in a StartDate as just a date without time as the calling convention. Inside the report I'm returning data for that day through midnight, or as you can see below, midnight minus 1 millisecond so that I don't grab any data from the next day
declare @StartDate datetime
declare @EndDate datetime
set @startdate = '12/16/08'
Set @EndDate = DateAdd(ms, -1, @StartDate + 1)
select @startdate, @enddate
I was expecting this to return 12/16/08 and 12/16/08 23:59:59, but instead, I get this:
The DateAdd syntax is correct, so what the heck? Changing the query to
Set @EndDate = DateAdd(ms, -2, @StartDate + 1)
then returns:
My guess is that with a resolution of 3 milliseconds it rounded the -1 down to 0, changing it to -2 tipped it the other way. A minor tweak as I probably don't have much data that happens precisely at midnight, but annoying that I didn't know/catch it at the time I wrote it. If I had used one second all would have been well (and not that one second would matter either!).
Posted by John Magnabosco on 22 December 2008
According to SQL Server 2005 Books On Line (BOL), the DateTime data type is described as follows:
"Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds)."
Which means that values are rounded to increments of .000, .003, or .007 seconds. In SQL Server 2008 The time, datetime2 and datetimeoffset data types allow a precision of
100 nanoseconds (.9999999).
Posted by Steve Jones on 22 December 2008
Very good catch. I wonder how often this might have been causing me issues in reports and I didn't realize it.
Posted by Jerry Hung on 22 December 2008
I am guessing there is a typo? second line should be -3 or -4?
first one is
Set @EndDate = DateAdd(ms, -1, @StartDate + 1)
second line is
Set @EndDate = DateAdd(ms, -1, @StartDate + 1)
I normally do -1 second
Posted by Andy Warren on 22 December 2008
Jerry, thanks for catching that!
Posted by alfonso.pg on 29 December 2008
Why not use this simple method:
dateCol BETWEEN @startDate AND @endDate
AND
dateCol != @endDate
This method removes the need for date math and rounding issues.
Posted by Andy Warren on 5 January 2009
Didnt think of it!
Posted by lukus_g on 5 April 2011
Interesting! i noticed this .997 today while going over a report :)
I've set my report with code similar to:
[code]
declare @start datetime
declare @end datetime
set @start = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) --first day of last month
set @end = DATEADD(MS,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) -- first day of this month
--select @start as 'Start time:'
--Select @end as 'End time:'
select [OPEN_TIME]
from dbo.table
where [OPEN_TIME] >= @start and [OPEN_TIME] < @end
[/code]
This gives me anything starting at .000 on the first day right up to .999 on the last.
But this raises a question from me, I'm now not sure if SQL can even store the datetime value '2011-03-31 23:59:59.999'
Would it then round that value to '2011-04-01 00:00:00.000' at insert?