Unprecise rounding when subtracting dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Unprecise rounding when subtracting dates

Author

Topic

ZuzuPetals
Starting Member

2 Posts

Posted - 2009-03-13 : 23:13:15

I am using SQL Server 2000... I am working on a query for a Cognos report that displays a start date and an end date. I then calculate the difference between the start date and end date to get the "Number of Days", unfortunately SQL Server is rounding up any remainder to an additional day.
For example:

Start Date: 2007-08-03 14:29:00.310
End Date: 2008-08-15 00:00:00.000
Number of Days equals 378.

The client doesn't like that it has rounded 377.39 up to 378. (They know the precise decimal, since they can do it in Excel. Argh!)

I used this expression for the calculation:
datediff(d,startdate,enddate)

I need the report to show 377, since it is less than 377.5.

I need a solution that I can either enter into the database view or into the report. I can create one calculation on top of another, on top of another to get the answer, if needed; but it pretty much needs to be able to calculate at runtime.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-14 : 01:00:08

I think u need the exact difference i.e no of day and hrs..

If so it will be helpful for u

select datediff(hh,'2009-03-13 10:00:00.310','2009-03-14 12:00:00.310')/24 as day,
datediff(hh,'2009-03-13 10:00:00.310','2009-03-14 12:10:00.310')%24 as hr

Regards

Senthil.C
Willing to update...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-14 : 01:46:01

Easy enough to do with the code below.

Notice how it rounds down to one day when the difference is 1 day 11:59:59.997, and up to 2 when the difference is 1 day 12:00:00.000.


select
	DayDiff = datediff(dd,0,EndDate-StartDate+'12:00:00.000'),
	*
from
    ( -- Test Data
    select  StartDate	= convert(datetime,'2007-08-03 14:29:00.310'),
	    EndDate	= convert(datetime,'2008-08-15 00:00:00.000')
    union all
    select  StartDate	= convert(datetime,'2007-08-03 14:29:00.310'),
	    EndDate	= convert(datetime,'2008-08-15 12:00:00.000')
    union all
    select  StartDate	= convert(datetime,'2007-08-03 00:00:00.000'),
	    EndDate	= convert(datetime,'2007-08-04 11:59:59.997')
    union all
    select  StartDate	= convert(datetime,'2007-08-03 00:00:00.000'),
	    EndDate	= convert(datetime,'2007-08-04 12:00:00.000')
    ) a

Results:
DayDiff     StartDate               EndDate
----------- ----------------------- -----------------------
377         2007-08-03 14:29:00.310 2008-08-15 00:00:00.000
378         2007-08-03 14:29:00.310 2008-08-15 12:00:00.000
1           2007-08-03 00:00:00.000 2007-08-04 11:59:59.997
2           2007-08-03 00:00:00.000 2007-08-04 12:00:00.000

(4 row(s) affected)

CODO ERGO SUM

ZuzuPetals
Starting Member

2 Posts

Posted - 2009-03-14 : 13:33:08

Wow, that works great! Thanks so much Michael!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources