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

• Hi!

I'm in extreme need of T-SQL function wich will round to the nearest even number, called banker's rounding or rounding to nearest (like Math.Round in .NET)

There are lots of discussions about rounding and realizations of banker's rounding in C#,VB,C++ but I haven't found any realization in SQL. Using exdended procedure is a solution but it's not to my liking.

I really can't think out it by myself.

Thanks in advance

• If you mean round to the nearest whole number, then simply converting to INT will do...

DECLARE @Amount1 MONEY

SET @Amount1 = 7.49

DECLARE @Amount2 MONEY

SET @Amount2 = 7.50

DECLARE @Amount3 MONEY

SET @Amount3 = 7.51

SELECT CAST(@Amount1 AS INT)

SELECT CAST(@Amount2 AS INT)

SELECT CAST(@Amount3 AS INT)

If you really mean to round to the nearest EVEN number (as you say, Banker's rounding), that is, the nearest number evenly divisible by 2, then this will do (the forumula)...

DECLARE @Amount1 MONEY

SET @Amount1 = 6.01

DECLARE @Amount2 MONEY

SET @Amount2 = 6.99

DECLARE @Amount3 MONEY

SET @Amount3 = 7.00

SELECT CAST(@Amount1/2 AS INT)*2

SELECT CAST(@Amount2/2 AS INT)*2

SELECT CAST(@Amount3/2 AS INT)*2

...and it appears to work correctly with negative numbers, as well...

DECLARE @Amount1 MONEY

SET @Amount1 = -6.01

DECLARE @Amount2 MONEY

SET @Amount2 = -6.99

DECLARE @Amount3 MONEY

SET @Amount3 = -7.00

SELECT CAST(@Amount1/2 AS INT)*2

SELECT CAST(@Amount2/2 AS INT)*2

SELECT CAST(@Amount3/2 AS INT)*2

--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.

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

• Thanks Jeff. But none of them is banker's rounding.

Banker's rounding bahaves like arithmetic rounding with one exception: if value is halfway between two numbers, one of which is even and the other odd, then the even number is returned.

Examples of banker's rounding (results of Math.Round in .NET):

Math.Round(3.454,2) -> 3.45

Math.Round(3.455,2) -> 3.46

Math.Round(3.445,2) -> 3.44

Math.Round(3.456,2) -> 3.46

Pay your attention on lines 2 and 3. Result depends on wether the second digit after dot is even or odd.

Complexity is that I perform calcualtions with double and finaly need to round the result. So values can have lots of digits after dot and I have to round them one by one to the precision digit.

• This SQL uses the banker's rounding algorithm:

DECLARE@Decimalssmallint

set@Decimals= 2

select BRAmt

,BRExpectedAmt

, CASE

WHEN 5 = ( ROUND(BRAmt * POWER( 10, @Decimals + 1 ) ,0,1) - (ROUND(BRAmt * POWER( 10, @Decimals ) ,0,1) * 10) )

AND 0 = cast( ( ROUND(BRAmt * POWER( 10, @Decimals ) ,0,1) - (ROUND(BRAmt * POWER( 10, @Decimals - 1 ) ,0,1) * 10) ) AS INTEGER ) % 2

then ROUND(BRAmt,@Decimals, 1 )

ELSE ROUND(BRAmt,@Decimals, 0 )

END as BRBankersRoundedAmt

FROM(select cast ( 3.454 as numeric(8,4) ) , cast(3.45 as numeric(6,2) ) union all

select cast ( 3.455 as numeric(8,4) ) , cast(3.46 as numeric(6,2) )union all

select cast ( 3.445 as numeric(8,4) ) , cast(3.44 as numeric(6,2) )union all

select cast ( 3.456 as numeric(8,4) ) , cast(3.46 as numeric(6,2) )

) as BR (BRAmt, BRExpectedAmt)

SQL = Scarcely Qualifies as a Language

• That's realy baker's rounding but with one weakness - it rounds only one digit before precision digit.

For instance: rounding of 3.445657545 to second digit after dot takes no account of digits on the right.

It should round digit by digit for the rightest to the precision digit. That's the main problem!

• So the banker's rounding algorithm must be applied from right to left? Try this:

create FUNCTION RoundBanker

( @Amtnumeric(38,16)

, @RoundToDecimal tinyint

)

RETURNS numeric(38,16)

AS

BEGIN

declare@RoundedAmtnumeric(38,16)

,@WholeAmtinteger

,@Decimaltinyint

,@Tennumeric(38,16)

set@Ten= 10.0

set@WholeAmt= ROUND(@Amt,0, 1 )

set@RoundedAmt= @Amt - @WholeAmt

set@Decimal= 16

While @Decimal > @RoundToDecimal

BEGIN

set @Decimal = @Decimal - 1

if 5 = ( ROUND(@RoundedAmt * POWER( @Ten, @Decimal + 1 ) ,0,1) - (ROUND(@RoundedAmt * POWER( @Ten, @Decimal ) ,0,1) * 10) )

and 0 = cast( ( ROUND(@RoundedAmt * POWER( @Ten, @Decimal ) ,0,1) - (ROUND(@RoundedAmt * POWER( @Ten, @Decimal - 1 ) ,0,1) * 10) ) AS INTEGER ) % 2

SET @RoundedAmt = ROUND(@RoundedAmt,@Decimal, 1 )

ELSE

SET @RoundedAmt = ROUND(@RoundedAmt,@Decimal, 0 )

END

RETURN ( @RoundedAmt + @WholeAmt )

END

GO

SQL = Scarcely Qualifies as a Language

• Thank you Carl!!! That's what I need.

• Andrew... This also works... and, it's set based.... just substitute a column name for @Number in the formula...

-- Math.Round(3.454,2) -> 3.45

-- Math.Round(3.455,2) -> 3.46

-- Math.Round(3.445,2) -> 3.44

-- Math.Round(3.456,2) -> 3.46

DECLARE @Number MONEY

DECLARE @precision INT

SET @precision = 2

SET @Number = 3.454

SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.455

SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.445

SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.456

SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

--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.

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

• Jeff - I think that may not work for the 3.445657545 example (which should go to 3.45).

Here's an alternative (non-looping) solution:

CREATE FUNCTION dbo.RoundBanker (@x money, @DecimalPlaces tinyint)

RETURNS money AS

BEGIN

set @x = @x * power(10, @DecimalPlaces)

return

case when @x = floor(@x) then @x

else

case sign(ceiling(@x) - 2*@x + floor(@x))

when 1 then floor(@x)

when -1 then ceiling(@x)

else 2*round(@x/2,0) end

end / power(10, @DecimalPlaces)

END

select dbo.RoundBanker(3.454,2) --> 3.45

select dbo.RoundBanker(3.455,2) --> 3.46

select dbo.RoundBanker(3.445,2) --> 3.44

select dbo.RoundBanker(3.456,2) --> 3.46

select dbo.RoundBanker(3.445657545, 2) --> 3.45

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

• Jeff and Ryan, try your solutions with these four test cases.

The differences between the pairs is in the second decimal position number being odd or even.

The difference between a pairs of numbers is in the fourth decimal position where the first is less than 5 and the second is greater than 5.

InAmt ExpectedAmt

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

3.4543 3.45

3.4546 3.46

3.4653 3.46

3.4656 3.47

3.4643 3.46

3.4646 3.46

The intermediate results from the looping solution are:

First pair:

Start with 3.4543

Banker's Round at 3 gives 3.454

Banker's Round at 2 gives 3.45

Start with 3.4546

Banker's Round at 3 gives 3.455

Banker's Round at 2 gives 3.46

Second Pair:

Start with 3.4653

Banker's Round at 3 gives 3.465

Banker's Round at 2 gives 3.46

Start with 3.4656

Banker's Round at 3 gives 3.466

Banker's Round at 2 gives 3.47

SQL = Scarcely Qualifies as a Language

• Ryan, You are correct.  It doesn't work correctly for 3.445657545.  It rounds to 3.44 instead of 3.45 as you stated.  Thanks for the catch and I apologize to everyone for not testing a bit deeper.  Back to the drawing board!

--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.

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

• Ryan,

Your code works great so long as the MONEY data type is used to hold the number and is probably appropriate for the Banker's Rounding problem because one must assume that bankers will round to the penny.

However, if the data type to hold the number is converted to the DECIMAL data type (to allow for rounding at, say, the 5th decimal place) and using the very same test number you gave me, it comes up with the same incorrect answer as mine did... here's the code I used...

--drop function dbo.RoundBanker

CREATE FUNCTION dbo.RoundBanker (@x decimal(38,20), @DecimalPlaces tinyint)

RETURNS money AS

BEGIN

set @x = @x * power(10, @DecimalPlaces)

return

case when @x = floor(@x) then @x

else

case sign(ceiling(@x) - 2*@x + floor(@x))

when 1 then floor(@x)

when -1 then ceiling(@x)

else 2*round(@x/2,0) end

end / power(10, @DecimalPlaces)

END

GO

SELECT dbo.RoundBanker (3.445657545 , 2) --<-- results in 3.44 instead of 3.45 as it should.

What an interesting problem...

Carl, I haven't explored your looping answer because I'm interested in accomplishing this "simple"  math problem without a loop of any kind... say, weren't you the one the recently tried to prove the hypothesis that UDF's should never be allowed in code save the one that Adam Mechanic wrote?  What's up with that?

--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.

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

• AND, dont' feel too alone on that MONEY v.s. DECIMAL thing... I just changed to the DECIMAL data type on my attempted code and ALL the answers became 3.45 some of which are obviously incorrect ... here's the code in case anybody else wants to "play"...

--Example numbers and the correct desired answers

-- Math.Round(3.445657545,2) -> 3.45 --<-- Ryan's test number

-- Math.Round(3.454,2)       -> 3.45

-- Math.Round(3.455,2)       -> 3.46

-- Math.Round(3.445,2)       -> 3.44

-- Math.Round(3.456,2)       -> 3.46

DECLARE @Number DECIMAL(38,20)

DECLARE @precision INT

SET @precision = 2

SET @Number = 3.445657545

SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.454

SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.455

SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.445

SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

SET @Number = 3.456

SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)

--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.

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

• BWAAA-HAAAA-HAAAA-HAAA! Snooorrtt! OOOOOOOHHHHH-WEEEEEE! UNBELIEVABLE!!!!  WE DON' NEED NO STINKIN' LOOPS... WE DON' NEED NO STINKIN' UDF.  WE DON' NEED NO STINKIN' CALCULATIONS!!!!  SQL SERVER HAS A FUNCTION TO DO BANKERS ROUNDING AUTO-MAGICALLY!!!!  I JUST CAN'T QUIT LAUGHING AT ALL THE STUFF WE WENT THROUGH AND IT'S RIGHT UNDER OUR NOSES!!!  GOTTA LOVE THOSE UNDOCUMENTED FEATURES!!!!

--===== If test table exists, drop it

IF OBJECT_ID('TempDB..#MyTemp') IS NOT NULL

DROP TABLE #MyTemp

--===== Create the test table

CREATE TABLE #MyTemp(Number DECIMAL (38,20))

--===== Populate the test table with data

INSERT INTO #MyTemp (Number) --Bankers Rounding expected

SELECT 3.456       UNION ALL --<3.46

SELECT 3.455       UNION ALL --<3.46

SELECT 3.454       UNION ALL --<3.45

SELECT 3.445657545 UNION ALL --<3.45

SELECT 3.445       UNION ALL --<3.44

SELECT 3.435       UNION ALL --<3.44

SELECT 3.425       UNION ALL --<3.42

SELECT 3.415       UNION ALL --<3.42

SELECT 3.405       UNION ALL --<3.40

SELECT 3.395                 --<3.40

--===== Demonstrate the "extremely complicated" solution

SELECT STR(Number,10,2)

FROM #MyTemp

HAAAA-HAAAA-HAAAA!!!! OH LORDY!  BILL GATES STRIKES AGAIN!!!  THIS IS TOO FUNNY!!!

Hey!  Anybody with SQL Server 7 or 2005... please try the code above and see if you get the same answers and let us know, please!  Thanks, alot!!! (Hee-hee-hee, I just can't stop laughing about this).

And now, my laughter turns to crys of sorrow because I just realized how many places me and my guys have used this damned function thinking that it rounded in a traditional sense.  Man! I've gotta lotta code to fix!!!!

{Edit}... Sorry folks... I posted some bad info... Serqiy figured out the error... I mistook rounding of the underlying FLOATS for Bankers' Rounding

` `

--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.

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

• Jeff, STR seems to round only last digit before precision. It doesn't take into account digits to the rigth.

Try this number: 3.4546

banker's round -> 3.46

STR -> 3.45

Viewing 15 posts - 1 through 15 (of 54 total)

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