As far as i know according to mathematics rounding should work as below when rounding number is 5.
2.435 => 2.44 (Round Up, if rounding to digit(3) is odd number)
2.445 => 2.44 (Round Down, if rounding to digit(4) is even number)
if we do summation all fine,
2.435 + 2.445 = 4.88
2.44 + 2.44 = 4.88
I'm pretty sure in .Net also rounding works like this.
But in SQL server, 5 is always rounding up which is not correct according to maths.
SELECT round(2.345, 2) = 2.35
SELECT round(2.335, 2) => 2.34
this results to 1 cent discrepancies in summation of rounded values.
2.345 + 2.335 = 4.68
2.35 + 2.34 = 4.69 => which is not correct
I have tried this with decimal and money data types.
Am i doing something wrong? Is there a work around for this?
If you do want to use banker's rounding in SQL Server...
CREATE FUNCTION BankersRounding(@value decimal(36,11), @significantDigits INT)
RETURNS MONEY
AS
BEGIN
-- if value = 12.345 and signficantDigits = 2...
-- base = 1000
declare @base int = power(10, @significantDigits + 1)
-- roundingValue = 12345
declare @roundingValue decimal(36,11) = floor(abs(@value) * @base)
-- roundingDigit = 5
declare @roundingDigit int = @roundingValue % 10
-- significantValue = 1234
declare @significantValue decimal(36,11) = floor(@roundingValue / 10)
-- lastSignificantDigit = 4
declare @lastSignificantDigit int = @significantValue % 10
-- awayFromZero = 12.35
declare @awayFromZero money = (@significantValue + 1) / (@base / 10)
-- towardsZero = 12.34
declare @towardsZero money = @significantValue / (@base / 10)
-- negative values handled slightly different
if @value < 0
begin
-- awayFromZero = -12.35
set @awayFromZero = ((-1 * @significantValue) - 1) / (@base / 10)
-- towardsZero = -12.34
set @towardsZero = (-1 * @significantValue) / (@base / 10)
end
-- default to towards zero (i.e. assume thousandths digit is 0-4)
declare @rv money = @towardsZero
if @roundingDigit > 5
set @rv = @awayFromZero -- 5-9 goes away from 0
else if @roundingDigit = 5
begin
-- 5 goes to nearest even number (towards zero if even, away from zero if odd)
set @rv = case when @lastSignificantDigit % 2 = 0 then @towardsZero else @awayFromZero end
end
return @rv
end
You're looking for Banker's Rounding - which is the default rounding in C# but is not how SQL Server ROUND() works.
See Why does TSQL on Sql Server 2000 round decimals inconsistently? as well as http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-rounding-methods and http://www.chrispoulter.com/blog/post/rounding-decimals-using-net-and-t-sql
Mathematically rounding up at 5 is correct, and also the most commonly used type of rounding in basic mathematics. Other types of rounding are also valid, but are not basic mathematics, but more often used in certain areas due to 0.5 often being a dispute number.
What you call mathematically rounding is actually bankers rounding, which is the type of rounding used in the finance business.
