CODEWITHSUNDEEP
sqlsql-server
Danny
Danny

Reputation: 2821

Rounding money to a decimal in SQL Server

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.

Upvotes: 2

Views: 9440

Answers (5)

Manas Sahu
Manas Sahu

Reputation: 41

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

Upvotes: 0

jeroenh
jeroenh

Reputation: 26782

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

Upvotes: 1

Gabe
Gabe

Reputation: 86708

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.

Upvotes: 2

Arvo
Arvo

Reputation: 10570

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

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147224

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

Upvotes: 0

Related Questions