CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Wijitha
Wijitha

Reputation: 1389

SQL Server Rounding Issue where there is 5

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?

Upvotes: 6

Views: 15388

Answers (4)

angularsen
angularsen

Reputation: 8668

Here is our version of banker's rounding, with some simplifications and assumptions on our max monetary amounts:

  • Round to 2 decimal places
  • Truncate input to decimal(10,4) (max value is ~1M or 999999.9999)
  • Return as decimal(8,2) (max value is ~1M or 999999.99)

Could also truncate to decimal(10,3), we just need the input to preserve enough decimal places for the rounding to work.

Adjust the decimal scale and precision to your needs.

The SQL function

CREATE FUNCTION dbo.fnBankersRound(@Num decimal(10,4))
RETURNS decimal(8,2)
AS
BEGIN
RETURN CASE
        WHEN ROUND(@Num, 2) - @Num = .005 AND ROUND(@Num, 2) % .02 <> 0
        THEN ROUND(@Num, 2) - 0.01
        ELSE ROUND(@Num, 2) 
    END
END

Some tests to validate the rounding

declare @val decimal(10,4);
set @val = 1.1149;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1150;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1249;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1250;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1349;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
set @val = 1.1350;
select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound

select
100000000.01 / 800000000.01 as Value,
round(100000000.01 / 800000000.01, 2) as Round,
dbo.fnBankersRound(100000000.01 / 800000000.01) as BankRound

Results image of the sql execution results

Original post at sqlservercentral.com

Upvotes: 1

Brock
Brock

Reputation: 266

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

Upvotes: 6

penguat
penguat

Reputation: 1357

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

Upvotes: 6

Amber
Amber

Reputation: 810

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.

Upvotes: 5

Related Questions