Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLAndy

I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.

Call It a Day, or a Day Minus 2 Milliseconds

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:

image

The DateAdd syntax is correct, so what the heck? Changing the query to

Set @EndDate = DateAdd(ms, -2, @StartDate + 1)

then returns:

image

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

Comments

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?

Leave a Comment

Please register or log in to leave a comment.