rounding in sql server produces strange results

Hi experts

I'm trying to round and calculate some numbers, but sql server yields very strange results.


If you have a look at the queries below and calculate it yourself, you'll get the same results. three times.
depending how you do the rounding.
the unrounded result would be 53.55, I personally would round to 53.6, which is also my desired result.

now.
query 1 produces 53.5
query 2 produces 53.6
query 3 produces 51

apart from the very strange result of query 3, what's really funny are the result of query 1 and 2, because they are almost identical. the only difference is, that query 1 uses user defined variables, while query 2 does not.

If I use a column (float) instead of variables the result is the same

I already tried to cast/convert the values etc.

Hope you have some ideas.

I'm using sql server 2005 express edition
declare @test float
set @test = 25.5
declare @margin float
set @margin = 5 

select round((@test + @test * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5/100))*2, 1)

Open in new window

LVL 3
ArikaelAsked:
Who is Participating?
 
cyberkiwiCommented:
select round((@test + @test * (5 *0.01))*2, 1)
test is float and cannot properly be represented in binary form exactly, so you get a value that is JUST UNDER 25.5.  What happens is that after all the calcs, it ends up just a little under 53.55 so it gets rounded down.

select round((25.5 + 25.5 * (5 *0.01))*2, 1)
25.5 here is of type NUMERIC, which retains decimal places and goes through decimal based maths, thereby preserving all the precision you expect

select round((25.5 + 25.5 * (5/100))*2, 1)
This one is simple. Try 5/100 and you will see. int/int = int
0
 
cyberkiwiCommented:
declare @test float
set @test = 25.5
declare @margin float
set @margin = 5

select round((convert(decimal,@test) + convert(decimal,@test) * (5 *0.01))*2, 1)
select round((convert(float,25.5) + convert(float,25.5) * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5/100.0))*2, 1)

reversal of fortunes
0
 
halfbloodprinceCommented:
I would do like this and it gives the desired results:

declare @test float
set @test = 25.5
declare @margin float
set @margin = 5

select round((@test + @test * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5 *0.01))*2, 1)
select round((25.5 + 25.5 * (5/100))*2, 1)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
halfbloodprinceCommented:
Sorry ignore the previous comment by me.It is supposed to be like this:
declare @test float
set @test = 25.5
declare @margin float
set @margin = 5

declare @Result Float
Set @Result = (Select ROUND((25.5 + 25.5 * (5 *0.01))*2,1))
Select @Result as Result
0
 
mdagisCommented:
Ok here is the full explanation:

1) The following returns for you 53.6 which is the correct since the result before round is 53.55.
select round((25.5 + 25.5 * (5 *0.01))*2, 1)

2) The one with the variable does not produces the correct result because the variables are not numeric (10,1). Try this and it will work:
declare @test numeric (10,1)
set @test = 25.5
select round((@test + @test * (5 *0.01))*2, 1)

3) The one with the division you have this result because the 5/100 = 0. Try doing select 5/100 = 0 and you will understand what I am saying. In order to work remove the parenthesis from the division like:

select round((25.5 + 25.5 * 5/100)*2, 1)




All of the above now should return 53.6

0
 
ArikaelAuthor Commented:
Hi experts

thanks for your answer. it works

so, I assume float is not a good choice when working with money?
should I better use decimal or money?
0
 
mdagisCommented:
I almost always use numeric(14,2)
0
 
cyberkiwiCommented:
For currency/money, use money.
For calculations on purely decimal values, use decimal.
Float  (double) for things where you really don't care about ending with a fixed precision and am likely to carry through the exact precision to the very end, such as lat/longitude, golden ratio, scientific calculations etc.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.