Why does TSQL on Sql Server 2000 round decimal numbers inconsistently?

Question

I am trying to calculate percentages of dollar values. At 50%, sometimes you get half a penny, and I need to round it to the nearest cent.

In Sql, my calculation is as follows:

round(retail * 0.5, 2, 0)

If I accept the following values, I get different results:

• 4.39
• 2.49

Without the environment, I get:

• 4.39 → 2.195
• 2.49 → 1.245

When rounding, I get:

• 2.195 → 2.19
• 1.245 → 1.25

I am told that this is a form of "rounding up bankers," but it seems that the value that affects the direction of rounding is an integer value.

My problem is that I expect the upper value to be rounded to 2.20. If this is really rounding up bankers, and it is affected by an integer value, does anyone have an example to prevent this behavior. If this is not rounding up bankers, can someone please provide an explanation and maybe a solution to just get the normal rounding behavior?

Thanks in advance!

Answer 1

What is the retail data type? I cannot test this on SQL 2000, but in SQL 2008 R2 I see similar results if I use float .

 float: 4.39 --> 2.195, rounds to 2.19 2.49 --> 1.245, rounds to 1.25 money: 4.39 --> 2.195, rounds to 2.20 2.49 --> 1.245, rounds to 1.25 decimal(5,2): 4.39 --> 2.195, rounds to 2.20 2.49 --> 1.245, rounds to 1.25 

---

To show the approximation created by the float , you can apply to decimal(20,19) , and it becomes clear why it is rounded as follows:

 4.39 * 0.5 --> 2.1949999999999999000 2.49 * 0.5 --> 1.2450000000000001000 

Answer 2

Take a look at some rounding methods here: SQL Server Rounding Methods Explains Bankers Rounding