Round to Even (aka Banker''s Rounding) - The final function

Question

Post reply

Round to Even (aka Banker''s Rounding) - The final function

Viewing 4 posts - 376 through 378 (of 378 total)

You must be logged in to reply to this topic. Login to reply

Suresh Rao SSC Rookie Points: 28 More actions · Answer 1

I have not found any function in SQL Server 2005 to peform bankers rounding.

You do have one in the Dot Net Framework

Math.Round Method (Decimal, MidpointRounding)

http://msdn2.microsoft.com/en-us/library/ms131274.aspx

If you are using SSRS you can use framework functions in the Report Designer.

One work around that worked for me is to increase the precision to 3-4 digits more than required ( I needed 6 , the i used 10 digits ) and then round the result. This does not work if the summation is for thousands of rows.

Bob Bobbity SSC Veteran Points: 210 More actions · Answer 2

Two questions please, having ploughed my way through this lesson in trolling:

1. Was there a final version of the banker's rounding algorithm which rounded correctly to 5 decimal places any "representation of a number" (to avoid concerns over imprecise representations of numbers) stored in a decimal(38,12) field? I have a real-world need, where one of our systems connects with another which is doing this rounding, and the SQL database is not.

I have an algorithm already (based I believe on one of those in this thread) which uses the power() function, and works fine except that this breaks down once the data being rounded is greater than 92,233,720,368,547.8 (rounded up from .746something), which is obviously less than the maximum possible in the decimal field. The example that I am failing on is 99999999999999.123565 to 5 decimal places, which should of course be 99999999999999.12356

2. We have clients based in New Zealand. Will they be arrested?

Bob Bobbity SSC Veteran Points: 210 More actions · Answer 3

Urrgh

I have just identified the issue as being with my function, and one that I corrected elsewhere some time ago. Please ignore.

For reference, below is the function being used, which handles the two cases posted on the first page of this thread as well as our regular requirement (rounding to 5 places)

CREATE FUNCTION dbo.RoundBanker (@val decimal(38,12), @pos int)

RETURNS decimal(38,12)

as

-- Takes two parameters. First is number to be rounded, second is how many places to round to

---------------------------------------------------------------------------------------------

begin

declare

@tmpval1bigint,

@tmpval2decimal(38,12),

@retvaldecimal(38,12),

@tmpval3decimal(38,12),

@tmpval4decimal(38,12),

@predecdecimal(38,12),

@postdecdecimal(38,12)

-- To get most from function, ignore everything before decimal point

--------------------------------------------------------------------

set @predec = floor(abs(@val))

set @postdec = case sign(@val) when 1 then @val - @predec else @val + @predec end

-- Actual work (Lynn's)

----------------------

set @tmpval1 = floor(abs(@postdec) * power(cast(10 as float), @pos))

set @tmpval2 = round(@postdec, @pos, 1)

set @tmpval3 = sign(@postdec) * (0.5 * power(cast(10 as float), (-1 * @pos)))

set @tmpval4 = (@postdec - @tmpval2)

set @retval = round(@postdec, @pos, case

when nullif(@tmpval1, (@tmpval1 / 2) * 2) is null

and ((@tmpval3 >= @tmpval4 and sign(@val) = 1)

or (@tmpval4 >= @tmpval3 and sign(@val) = -1))

then 1

else 0

end)

-- Rebuild number

-----------------

select @retval = case sign(@val) when 1 then @retval + @predec else 0.0 - @predec + @retval end

return @retval

end

angularsen Grasshopper Points: 13 More actions · Answer 4

Cross-post from another thread, more details there:

https://www.sqlservercentral.com/forums/topic/bankers-rounding-in-t-sql-like-math-round-in-net/page/4#post-4258087

This is the function we currently use, with some assumptions and simplifications.

We only need to represent values below 1M and we always round to 2 decimal places.

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