Calculations with time and then cast to decimal

[joacker]

Hello,

i have the following statement:
SELECT AVG(ROUND(Datediff(ss,start,end)/3600),2))

i want to have the following output:
1,27 hours
2,19 hours
1,03 hours
0,28 hours
5,93 hours

As start and end variables are time datatypes i have to cast it to a decimal to get the desired output, don't i?

So i tried this:
SELECT AVG(ROUND(Cast(Datediff(ss,start,end)/3600 as decimal(15,2)),2))

But it doesn't deliver the desired output:
1,27154 hours
2,19081 hours
1,03333 hours
0,28000 hours
5,93413 hours

What am i doing wrong here?

Thx 4 any help

 

[gmmastros]

This is certainly a data type problem. I think the biggest problem you are having is with integer math. DateDiff returns an integer, and your 3600 is cast (auto-magically for you) as an integer.

[tt][blue]Datediff(ss,start,end)/3600[/blue][/tt]

From a data type perspective you have INT/INT which results in an integer. To fix this, you could cast one (or both) to a decimal before dividing. There is another trick you could use to force decimal division (instead of integer division).

[tt][blue]Datediff(ss,start,end)/3600[!].0[/!][/blue][/tt]

3600.0 will auto-magically be cast as a decimal(5,1). Then the rules for precision and scale come in to play so your result will likely be something like Decimal(10,6).

Then, I suggest you take the average first and then round or cast as a decimal to get your 2 digits of precision that you want.

I suggest you try this:

SELECT Cast(Avg(Datediff(ss,start,end)/3600.0) As Decimal(10,2))

Basically... force decimal division, then average the values, and finally cast as decimal.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

SELECT AVG(ROUND(Cast(Datediff(ss,start,end)/3600 as float),4))

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[joacker]

Thank you very much guys. I am always impressed by the great support here.