Using DateDiff to find hours but not rounding

[AccessUser22]

Hello,

I have a field that stores StartTime and a field that stores EndTime in my database. I want to get the total hours so I'm using DateDiff(hour, starttime, endtime) but that's giving me a rounded number of hours. I want the hour and minutes in decimal but rounding up every 15 minutes. Example if the datediff equals 5 hours and 5 minutes, I would want to see it displayed as 5.25. Anyone know how to do this?

 

[gmmastros]

Lot's of fun stuff here.

Declare @Start DateTime
Declare @End DateTime

Set @Start = '20061102 10:00AM'
Set @End = '20061102 2:05PM'

Select Ceiling(DateDiff(Minute, @Start, @End) / 60.0 * 4.0) / 4.0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

I think you're going to have to do this in two parts.

Declare @hours int, @mins int

Set @hours = DatePart(hh,startdate,enddate)

Set @mins = DatePart(mi,startdate,enddate)

Select 'This has taken ' + Convert(varchar(2),@hours) + '.' +
CASE WHEN @mins > 0 and @mins <= 15 THEN '25'
WHEN @mins > 15 and @mins <= 30 THEN '50'
WHEN @mins > 30 and @mins <= 45 THEN '75'
WHEN @mins > 45 and @mins <=60 THEN '00' END 
+ ' hours to process.'

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Wow. Why do I always take the long way 'round? @=)

Nevermind. Use George's code. Is faster. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Hey George,
What does the Ceiling function do?


- Paul
- Database performance looks fine, it must be the Network!

 

[AccessUser22]

Very nice George, Works great. Thanks!

 

[ptheriault]

Never mind... I found it in BOL...
Did you memorize BOL at some point.. ; )

- Paul
- Database performance looks fine, it must be the Network!

 

[gmmastros]

Did you memorize BOL at some point.. ; )

Nope. I use flashcards [small]in the powder room[/small].

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Well George you and your flash cards get a star for that knowledge of the function.


- Paul
- Database performance looks fine, it must be the Network!

 

[ESquared]

Just be aware that the Datediff version is counting minute boundaries instead of minutes elapsed, so can be up to almost a minute off.

Declare @Start DateTime
Declare @End DateTime

Set @Start = '20061102 10:00:00 AM'
Set @End = '20061102 2:15:59.997 PM'

PRINT Ceiling(Convert(float, @End - @Start) * 96) / 4

--Result:
4.5

This version, rather than counting the number of whole minute boundaries crossed, uses whatever precision the server is offering. SQL Server theoretically uses 1/300th of a second but in practice it is a little larger.

The DateDiff method with this same pair of datetimes is 4.25. It would be incorrectly low by .25 for almost 60 seconds from 2:15:01 PM to 2:15:59.997 PM.