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!).