Today I discovered an arithmetical quirk called banker's rounding.
My .NET 3.5 website, written in C#, performs some calculations which, because they deal with currency, round to two decimal places. Here's an example:
double saleAmount = 205.75;
double commissionPercentage = 6.0;
double commission = Math.Round(saleAmount * commissionPercentage / 100.0, 2);
The result of the calculation is stored in a database. I had reason to believe there was a logic error in my application, so I wrote a database script to verify some of the calculations by directly examining values in the database. Sometimes the value calculated by the T-SQL script in SQL Server Management Studio 2005 was off by one cent when compared to the value calculated in C#.
For example, in C#, Math.Round(12.345, 2) is 12.34, while in T-SQL, Round(12.345, 2) is 12.35 -- one cent higher.
I learned in science class that, when the value to be rounded ends in 5, you round up. It turns out that .NET takes a different approach, called banker's rounding. It's based on IEEE Standard 754, section 4, and here's Microsoft's explanation of it. In short, when the last digit is a 5, it rounds up if the preceding digit is odd, and down if even. This is intended to minimize rounding errors that would accumulate from consistently rounding a midpoint value in the same direction.
SQL Server does not perform banker's rounding by default. In fact, I don't think there's any built-in function in SQL Server to perform banker's rounding. A number of people have posted their own SQL functions for banker's rounding. I'm not sure all of them are correct.
Rather than find or write a banker's rounding function in T-SQL, I worked around the problem. All I needed was to identify values that were way off from the expected value. So I used a SQL comparison like this to find values that differed by more than one cent: IF ABS(@expectedSalesCommission - @amount) > 0.01...
That turned out to not quite work. Because floating-point calculations are imprecise, sometimes values the computed value of @expectedSalesCommission was a trifle more than one cent away from the computed value of @amount. I changed it to IF ABS(@expectedSalesCommission - @amount) > 0.011..., and that did the trick.
