Math rounding issue - 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

Math rounding issue

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2013-05-30 : 18:15:55

Hi

I've got the following line of code:

SET @TotalActualHoursTraining = (SELECT SUM(classDurationInMinutes) FROM @vt_MemberClassesCalendar WHERE absenceType = 0) / 60

TotalActualHoursTraining is a decimal(10,2)

I know for sure the select statement returns 270

The code runs fine, but I'm getting back 4.00, when I should be getting back 4.5

I've even tried:

SET @TotalActualHoursTraining = 270 / 60

... and I'm still getting 4.0!

Any ideas where I'm going wrong?

Thanks as always

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-30 : 18:18:31

Somewhere you are doing integer division. Try these:

SET @TotalActualHoursTraining = (SELECT SUM(classDurationInMinutes) 
FROM @vt_MemberClassesCalendar WHERE absenceType = 0) / 60.0

SET @TotalActualHoursTraining = 270.0 / 60

In each case we are forcing the division to be non-integer type.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-31 : 01:59:27

I think reason is you're doing integer division first and then while assigning it gets converted to decimal. As James suggested, do the explicit conversion to decimal in division itself by making divisor as 60.0 and it should then undergo decimal division giving you the decimal result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2013-05-31 : 09:24:11

Perfect. Thank you both - making the number 60.00 rather than just 60 worked!

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 05:09:20

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-06-02 : 12:52:52

Also refer this post http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources