Rounding Seconds to Hours - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Rounding Seconds to Hours

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-03 : 11:49:13

I am having a brain cramp on a very simple calc.
Start Date-Time - 2009-09-02 10:32:38.000
End Date Time - 2009-09-02 13:32:37.820

Calc Seconds - datediff(second, startdate, enddate) = 10799
So far so good
Get the unrounded hours
datediff(second, startdate, enddate)/3600 = 2

ACTUAL Result is 2.9997222222222222222222222222222
Now my problem... Rounding to nearest hour
ROUND(datediff(second,C_Resolved_Time,Rn_Create_Date)/3600,0)
still gives me 2 hours but
ROUND(datediff(second,C_Resolved_Time,Rn_Create_Date)/3600,-1)
gives me zero.

My expected result is "3"

Obviously I am using ROUND incorrectly. Anyone out there willing to set me straight?

John

"The smoke monster is just the Others doing barbecue"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-03 : 11:52:50

why dont you just use
datediff(hh, startdate, enddate)

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-03 : 11:58:15

LOL, because the MSDN web site tutorial showed the seconds to hours calc and never mentioned starting at hours PLUS the fact I never thought of that... Boy I hate when the answers are that easy.

THANKS!

John

"The smoke monster is just the Others doing barbecue"

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-03 : 12:11:53

Hopefully someone will still look here so I can also understand what I am just not getting regarding the ROUND function.
What should I be using instead of:
ROUND(2.9997222222222222222222222222222, 0)
to get the expected result of "3"?

John

"The smoke monster is just the Others doing barbecue"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-03 : 12:28:23

quote:
Originally posted by JCirocco

Hopefully someone will still look here so I can also understand what I am just not getting regarding the ROUND function.
What should I be using instead of:
ROUND(2.9997222222222222222222222222222, 0)
to get the expected result of "3"?

John

"The smoke monster is just the Others doing barbecue"

Round returns a 3 as you wanted, but the datatype will be a decimal, just like the number you are rounding. If you want an integer, you have to cast it to an integer.

select	x1 = ROUND(2.9997222222222222222222222222222, 0),
	x2 = convert(int,ROUND(2.9997222222222222222222222222222, 0) )

Results:

x1                                 x2
---------------------------------- ----------- 
3.0000000000000000000000000000000  3

CODO ERGO SUM

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-03 : 12:32:37

Interesting. I asm doing this within Reporting Services and I was getting "2" as a result. I appreciate the help from everyone here.

John

"The smoke monster is just the Others doing barbecue"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-03 : 12:36:35

Rounding in Reporting Services is completely different than rounding in SQL.

CODO ERGO SUM

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-03 : 12:45:41

OK, Based on the original question above, I just went to SQL Server Mgr Console and typed:

select round(10799/3600, 0), (10799/3600) AS Calc

and received:
2 2

John

"The smoke monster is just the Others doing barbecue"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-03 : 12:50:34

quote:
Originally posted by JCirocco

OK, I just went to SQL Server Mgr Console and typed:

select round(10799/3600, 0), (10799/3600) AS Calc

and received:
2 2

John

"The smoke monster is just the Others doing barbecue"

That is completly different that your other example.

Integer math, 10799/3600, returns an integer result, 2, and that has nothing to do with the round function.

See what happens when you use decimal numbers, instead of integers:

select round(10799./3600.,0)

CODO ERGO SUM

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-03 : 13:40:05

WOW, so when I did my original

datediff(second, start, end)

the resulting value it returned must have been an integer and not a decimal.

I have so much to learn...

John

"The smoke monster is just the Others doing barbecue"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources