SQL Server Rounding Issue

I'm using SQL Server 2005. And I'm using ROUND T-SQL function to round a decimal column value. But it seems that the rounded value is incorrect.

    PRINT ROUND(1890.124854, 2) => 1890.120000

As shown the ROUND function is returning 1890.12 whe开发者_如何学Cre as it should be 1890.13. Does anyone encountered this and what should be the correct way of rounding so that I get the expected value 1890.13..?

Thanks.

---

ROUND() is working as it was intended to. You specified to round to 2 decimal places, and that's what you got.

Returns a numeric value, rounded to the specified length or precision.

Rounding means that a digit of 5 or above goes up to nearest, less than 5 down to nearest.

so,

PRINT ROUND(1890.125000, 2) 

produces 1890.130000

Whereas

PRINT ROUND(1890.124999, 2) 

produces 1890.120000

---

Your rounding issue is related to the rounding algorithm used by SQL Server. I believe SQL Server uses the "Round to Even" (sometimes known as Banker's Rounding) algorithm. In Banker's Rounding, a digit get rounded down if the least significant digit to the right of it is less than five or rounded up if the least significant digit to the right of it is greater than five. If the least significant digit to the right of it is equal to five, then the digit to the left of the five is rounded up to the nearest even number.

In your example of 1890.124854, as the rounding begins at the right-most digit and works to the left, the 8 causes the 4 to the left of it to get rounded up to 5. The five has an even number (2) to the left of it so, since it's already even, it leaves it alone. Thus, rounding to two decimal places should yield 1890.12. However, if your example was instead 1890.134854, then as the rounding works from right to left, the 8 rounds the 4 up to 5 and then the 3 next to the 5 gets rounded up to the next even number which is 4. The result of rounding to two decimal places should then yield 1890.14.

The theory is that 1890.125 is neither closer to 1890.12 or 1890.13. It is exactly in between. Therefore, to always round up every digit to the left of a 5 would give an undesired upward bias that can skew calculations toward an artificially high result. This bias upward becomes more exaggerated in complex calculations or those involving multiple iterations where a five as the least-significant digit may be encountered numerous times. However, in general calculations, the number to the left of 5 is statistically just as likely to be odd as even. Because of this, rounding to the even number causes the calculation to statistically hover close to the true mean of the rounded number.

Anymore, almost everything uses this "Round to Even" algorithm. Many years ago, I used to develop in a programming language that didn't. It used the more "traditional" rounding where everything to the left of a 5 got rounded up, regardless of being odd or even. We ran into the biasing problem I mentioned above.