# Banker''s rounding in T-SQL (like Math.Round in .NET)

• Heh... flies haven't found it, yet... :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Here's my version of Banker's Rounding.

I tried to make it similar to existing ROUND, which can do SELECT ROUND(250, -2). Arithmetic Rounding rounds 250 to 300.

SELECT dbo.f_BankersRound(250, -2) yields 200.000000...

This implementation works for input up to 16th decimal, and is not hardcoded to money type.

Please let me know if you find any bug with it.

(I found a bug, and reposted on 10/10)

-- =============================================

-- Author Yu Yokota

-- =============================================

CREATE FUNCTION [dbo].[f_BankersRound](

@aInput DECIMAL(38, 16),

RETURNS DECIMAL(38, 16)

AS

BEGIN

DECLARE @isNegative BIT;

DECLARE @inputAbs DECIMAL(38, 16);

DECLARE @leftPart DECIMAL(38, 16);

DECLARE @rightPart DECIMAL(38, 16);

DECLARE @halfWay DECIMAL(38, 16);

DECLARE @ten DECIMAL(38, 16);

-- Separate the input into @isNegative and @inputAbs

IF @aInput < 0

BEGIN

SET @isNegative = 1;

END ELSE

BEGIN

SET @isNegative = 0;

END

SET @inputAbs = ABS(@aInput);

-- Truncate the aInput and store it as @leftPart

SET @leftPart = ROUND(@inputAbs, @aDecimalPlace, 1);

-- Store the part to be rounded as @rightPart

SET @rightPart = @inputAbs - @leftPart;

-- Calculate the halfway point for rounding

SET @ten = 10;

SET @halfWay = POWER(@ten, -@aDecimalPlace) * 0.5;

-- If the @rightPart is not exactly half way,

-- the result is the same as the Arithmetic Rounding

IF @rightPart <> @halfWay

BEGIN

END -- IF

-- If the last digit of the @leftPart is odd,

-- the result is the same as the Arithmetic Rounding

IF (@leftPart * 0.5)

<> ROUND(@leftPart * 0.5, @aDecimalPlace, 1)

BEGIN

END

-- If the last digit is even, Truncate

IF @isNegative = 1

BEGIN

RETURN -@leftPart

END

RETURN @leftPart

END

go

• Anyone check out the other threads that we had on this topic? I thought we had a solution to this somewhere in there with all the mud flying around.

• This is the function I had posted back during the war:

CREATE FUNCTION [dbo].[fn_BRound] (

@val decimal(38,20),

@pos int

)

RETURNS decimal(38,20)

as begin

declare @tmpval1 int,

@tmpval2 decimal(38,20),

@retval decimal(38,20),

@tmpval3 decimal(38,20),

@tmpval4 decimal(38,20)

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

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

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

set @tmpval4 = (@val - @tmpval2)

set @retval = round(@val, @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)

return @retval

end

• using select dbo.fn_BRound(5.344515,2) gives you 5.34 and not 5.35. Any clues on how I can get 5.35 from this number?:crying:

• pbushong (3/1/2008)

using select dbo.fn_BRound(5.344515,2) gives you 5.34 and not 5.35. Any clues on how I can get 5.35 from this number?:crying:

You won't. You are rounding to the 100ths decimal place, even standard rounding will return 5.34 not 5.35. The reason is the value in the 1000th's place is a 4 (5.344), and this causes the value to round down to 5.34.

😎

• Thank you for your feedback. I was trying to find a function in sql that would give me the same answer like roundup does in excel.

Example: If I use round(5.344515,2) in excel I get 5.34

and if I use roundup(5.344515,2) in excel I get 5.35.:crazy:

• A clarification on Banker's rounding for everyone - 3.4546 rounded to two decimals IS NOT 3.46. It should be 3.45 - you do not work backwards and round from right to left. You only need the next digit. The idea behind banker's rounding is that you will eventually end up with even amounts when splitting pennies - therefore the whole theory in the Office Space millions is blown out of the water unless they weren't actually rounding.

So I've found Ryan's function to work perfectly.

• Just wanted to say that I've been attempting to find a quick way to implement half even rounding in SQL, because I wanted to replicate the numbers generated by Java. Lynn's function worked perfectly, so many thanks for that. Ryan's implementation higher up the chain did not work as expected when rounding 2.507186 to 5 decimal places. As you might have guessed from the fact that I spent several hours trying to find someone else's implementation rather than making my own, I don't have the knowledge to explain why Ryan's function rounds down...

I actually spent most of the day reading over the several threads on this topic. It made for some interesting reading, but that had more to do with the blatant trolling than anything else.

• I am glad that this old thread was of use to you, and I am glad my routine was useful as well.

Thank you for feedback.

• Here is another solution. This technique can be used in a function or directly inline within a select as shown here.

select dec, value,

round(value+((mod(trunc(power(10,dec)*value),2)-sign(value))*power(10,-length(value-trunc(value)))),dec) "Banker's Round"

from

(

select 2 dec, 0 value from dual union all

select -2, 1650 from dual union all

select -2, 1750 from dual union all

select -3, 23500 from dual union all

select -3, 24500 from dual union all

select -2, -1650 from dual union all

select -2, -1750 from dual union all

select -3, -23500 from dual union all

select -3, -24500 from dual union all

select 2, 7.345 from dual union all

select 2, 3.454 from dual union all

select 2, 3.455 from dual union all

select 2, 3.445 from dual union all

select 2, 3.456 from dual union all

select 2, 3.4543 from dual union all

select 2, 3.4546 from dual union all

select 2, 3.4653 from dual union all

select 2, 3.4656 from dual union all

select 2, 3.4643 from dual union all

select 2, 3.4646 from dual union all

select 8, 3.445657545 from dual union all

select 2, 3.445 from dual union all

select 2, 3.435 from dual union all

select 2, 3.425 from dual union all

select 2, 3.415 from dual union all

select 2, 3.405 from dual union all

select 2, 3.395 from dual union all

select 2, -7.345 value from dual union all

select 2, -3.454 from dual union all

select 2, -3.455 from dual union all

select 2, -3.445 from dual union all

select 2, -3.456 from dual union all

select 2, -3.4543 from dual union all

select 2, -3.4546 from dual union all

select 2, -3.4653 from dual union all

select 2, -3.4656 from dual union all

select 2, -3.4643 from dual union all

select 2, -3.4646 from dual union all

select 8, -3.445657545 from dual union all

select 2, -3.445 from dual union all

select 2, -3.435 from dual union all

select 2, -3.425 from dual union all

select 2, -3.415 from dual union all

select 2, -3.405 from dual union all

select 2, -3.395 from dual

)

• The speed is poblem !!

This funkction is about 10 times slower then standard round()

• softech (4/10/2012)

The speed is poblem !!

This funkction is about 10 times slower then standard round()

That is one of the other factors for this: a different solution is required to do bankers Rounding vs Regular rounding, so Regular Rounding cannot be used.

I don't think anyone has optimized the function examples provided so far, so it will not perform as well as a built in, native function.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• If anyone is interested, I just rewrote my scalar function as an inline table valued function:

create function dbo.itvf_BRound(

@val decimal(38,20),

@pos int

) returns table with schemabinding

as return(

with BaseComp1 as (

select

cast(floor(abs(@val) * power(cast(10 as float), @pos)) as int) tmpval1,

cast(round(@val, @pos, 1) as decimal(38,20)) tmpval2,

cast(sign(@val) * (0.5 * power(cast(10 as float), (-1 * @pos))) as decimal(38,20)) tmpval3

),

BaseComp2 as (

select

cast((@val - tmpval2) as decimal(38,20)) tmpval4

from

BaseComp1)

select

cast(round(@val, @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) as decimal(38,20)) val

from

BaseComp1 cross join BaseComp2

);

• Here's what I came up with. Granted I made a pretty sweeping assumption (always rounding to hundredths), but that was the business case I was working with. It would be easy enough to add a precision parameter and replace some of the hardcoded #s with the calculations used to derive them, but for the typical use (actual bankers rounding to the nearest cent) this seems to work.

CREATE FUNCTION dbo.itvf_BankersRound(@Num decimal(38,9))

RETURNS TABLE

AS

RETURN

(

SELECT

CASE

WHEN ROUND(@Num, 2) - @Num = .005

THEN

ROUND(@Num, 2) + CASE

WHEN

ROUND(@Num, 2) % .02 <> 0

AND ROUND(@Num, 2) > @Num

THEN -0.01

WHEN

ROUND(@Num, 2) % .02 <> 0

AND ROUND(@Num, 2) < @Num

THEN .01

ELSE 0

END

ELSE ROUND(@Num, 2) END AS [Result]

)

GO

Viewing 15 posts - 31 through 45 (of 54 total)