Rounding money to a decimal in SQL Server

Question

I am trying to round to 3 decimal places from two variables of type money in SQL Server 2008.

DECLARE @Val1 money = 554.5344
DECLARE @Val2 money = 84020.37
DECLARE @Result decimal(6,5) = @Val1/@Val2 *100
SELECT @Result

The result above is 0.65. With a calculator it is 0.65999995001212, I need the result to be 0.659.

Answer 1

If you want 0.659999 to be 'rounded' to 0.659, you're truncating, not rounding...

Also, you probably want to use the standard ROUND function, which can be used for truncating as well. See the answers to this question

Answer 2

To get the same result as a calculator, you need to use FLOAT, not MONEY:

DECLARE @Val1 float 
DECLARE @Val2 float
SET @Val1= 554.5344
SET @Val2= 84020.3700
DECLARE @Result float
SET @Result = @Val1/@Val2 *100
SELECT @Result

gives: 0.65999995001212

Answer 3

Result can't be 0.659 - exact result rounded to 5 places is 0.66000.

And you should never divide money by money :) At least not directly; correct result can be obtained as follows:

DECLARE @Val1 money = 554.5344
DECLARE @Val2 money = 84020.37
DECLARE @Result decimal(6,5) = cast(@Val1 as float)/@Val2*100
SELECT @Result

Answer 4

Try this:

DECLARE @Val1 money = 554.5344 
DECLARE @Val2 money = 84020.37 
DECLARE @Result decimal(6,3) = @Val1 * 100 / @Val2
SELECT @Result

Your problem is that MONEY has only 4 decimal places, so when you divide 554.5344 by 84020.37 you get 0.0065, so when you multiply it by 100 you get 0.6500. If you multiply by 100 first, you are dividing 55453.44 by 84020.37 and getting 0.659, which is what you want.

Answer 5

To get the all the values after decimal you should to use float. Then use round[1] function.

DECLARE @Val1 float
DECLARE @Val2 float SET @Val1= 554.5344 SET @Val2= 84020.3700 DECLARE @Result float SET @Result = round(@Val1/@Val2 *100, 3) SELECT @Result

Visit my Blog: http://expertdevelopersblog.blogspot.com/