Sql rounding does not always round

Question

I am trying to make a round in sql, but the result is very strange; that's what I'm doing:

I am using Mssql

I have a choice:

Select num1, num2, num1 * num2, round(num1 * num2, 2) from myTable 

Data type:

num1 is a float

num2 is money

if num1 is 15 and num2 is 0.033 Here are my results:

15, 0.033, 0.495, 0.5

The problem is when I have these values:

if num1 is 5 and num2 is 0.045 Here are my results:

5, 0.045, 0.225, 0.22

Does anyone have an idea?

Answer 1

I converted my float column to money and it seems to be working now.

Thank you everybody!

Answer 2

If you look at this running example in grid view (note also that the text mode is different), you should see the problem:

 DECLARE @tbl AS TABLE (num1 float, num2 money); INSERT INTO @tbl VALUES (15, 0.033), (5, 0.045); Select num1, num2, num1 * num2, round(num1 * num2, 2) from @tbl; 

Note that the number is indeed .224999999, which is rounded to 0.22 - you need to be careful when viewing SQL Server non-binary output - it sometimes converts numbers to text in a way that you do not expect.

This comes from the float * money operation, resulting in a float that is prone to binary representation problems.

You should think about what this means for your float value, and consider using a decimal number, and consider scaling and precision rules for decimal operations . Note that money is only somewhat similar to decimals (19, 4)

Answer 3

You can try to use a long type instead of a float. I think this is a problem with floats.

Answer 4

What version of SQL are you using?

Try this code in Query Analyzer

 DECLARE @num1 float DECLARE @num2 money SET @num1 = 15 SET @num2 = 0.033 SELECT @Num1, @Num2, @Num1 * @Num2, ROUND(@Num1 * @Num2,0), ROUND(@Num1 * @Num2,1), ROUND(@Num1 * @Num2,2), ROUND(@Num1 * @Num2,3) 

Float is an approximation, not a real number. Use Decimal instead.

 DECLARE @num1 decimal DECLARE @num2 money SET @num1 = 5 SET @num2 = 0.045 SELECT @Num1, @Num2, @Num1 * @Num2, ROUND(@Num1 * @Num2,2), ROUND(CAST(@Num1 as money) * @Num2,2)