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

• Sorry, Jeff, but the use of STR does not work as the expected results are incorrect. With Banker's Rounding to 2 decimal points:

For 3.455, since the third decimal position value is 5 and the second decimal position is odd, this should truncate, not round. The expected results should be 3.45 not 3.46

SQL = Scarcely Qualifies as a Language

• Carl wrote: "For 3.455, since the third decimal position value is 5 and the second decimal position is odd, this should truncate, not round. The expected results should be 3.45 not 3.46"

Not true, Carl... you need to go back and read about Bankers Rounding in the original post from Andrew... the following is a copy of part of that post to refresh your memory... I've highlighted the important part so you can find it

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

According to what Andrew has stated in other posts, if the number ends in precisely 5 mils, then the number is supposed to be rounded to the nearest even hundreth.

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

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

• Andrew, you're making a classic error... you are doing traditional rounding to 3 places before you are attempting the bankers rounding....

Just like the example of 3.445657545 being rounded to 3.45 instead of 3.44 because of the digits that follow the mils position, so must you consider the digits following the mils of 3.4546... since the original does NOT precisely end with 5 mils, Bankers Rounding is not applicable in this instance.  At least according to your original definition of Bankers Rounding... here's some code the demonstrates the proper consistency of the Bankers Rounding associated with STR...

--===== 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)

SELECT 3.4600 UNION ALL

SELECT 3.4590 UNION ALL

SELECT 3.4580 UNION ALL

SELECT 3.4570 UNION ALL

SELECT 3.4560 UNION ALL

SELECT 3.4550 UNION ALL

SELECT 3.4549 UNION ALL

SELECT 3.4548 UNION ALL

SELECT 3.4547 UNION ALL

SELECT 3.4546 UNION ALL

SELECT 3.4545 UNION ALL

SELECT 3.4544 UNION ALL

SELECT 3.4543 UNION ALL

SELECT 3.4542 UNION ALL

SELECT 3.4541 UNION ALL

SELECT 3.4540 UNION ALL

SELECT 3.4530 UNION ALL

SELECT 3.4520 UNION ALL

SELECT 3.4510 UNION ALL

SELECT 3.4500 UNION ALL

SELECT 3.4490 UNION ALL

SELECT 3.4480 UNION ALL

SELECT 3.4470 UNION ALL

SELECT 3.4460 UNION ALL

SELECT 3.4450 UNION ALL

SELECT 3.4449 UNION ALL

SELECT 3.4448 UNION ALL

SELECT 3.4447 UNION ALL

SELECT 3.4446 UNION ALL

SELECT 3.4445 UNION ALL

SELECT 3.4444 UNION ALL

SELECT 3.4443 UNION ALL

SELECT 3.4442 UNION ALL

SELECT 3.4441 UNION ALL

SELECT 3.4440 UNION ALL

SELECT 3.4430 UNION ALL

SELECT 3.4420 UNION ALL

SELECT 3.4410 UNION ALL

SELECT 3.4400

--===== Demonstrate the Problem

SELECT Number AS Original,

STR(Number,10,2) AS Bankers,

FROM #MyTemp

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

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

• Jeff, you are right - STR behaves like Math.Round in .NET. But I found one exception I can't explain:

In .NET:

Math.Round(40.645161290322584,2) --> 40.65

Math.Round(41.645161290322584,2) --> 41.65

Math.Round(40.645,2) --> 40.65

Math.Round(41.645,2) --> 41.64

STR in SQL:

select str(40.645161290322584, 10, 2) --> 40.65

select str(41.645161290322584, 10, 2) --> 41.65

select str(40.645, 10, 2) --> 40.65

select str(41.645, 10, 2) --> 41.65

If someone knows HOW IT ROUNDS digits that follow the mils position - please explaine me - I'm absolutely confused with it.

• Also, I've found "banker's" round in VB from Microsoft

http://support.microsoft.com/default.aspx?scid=kb;EN-US;196652

I've translated it to T-SQL, here it is:

create function BankRoundDotNet (@val as numeric(30,15), @factor as int)

returns money

as

begin

declare @result double precision

declare @temp as double precision,

@fix_temp as double precision

set @temp = @val * @factor

set @fix_temp = floor(@temp + 0.5 * sign(@val))

if (@temp - cast(@temp as int) = 0.5)

begin

if ((@fix_temp / 2) cast(@fix_temp / 2 as int))

begin

set @fix_temp = @fix_temp - sign(@val)

end

end

set @result = @fix_temp / @factor

return @result

end

• I've investigated it: seems like that's due to inaccuracy with double - very small amounts (like 0.0000000005) are added while calculating. So, I think it' s problem with double calcualations.

I need to explain "banker's" rounding to QA staff. How can I explain it in a simply?????

• Easy...

1.  Any decimal dollar amount greater than x.xx5, regardless of the number of decimal places, will be rounded up to the penny.

2.  Any decimal dollar amount less than x.xx5, regardless of the number of decimal places, will be rounded down to the penny.

3.  Any decimal dollar amount precisely equal to x.xx5 (trailing zeroes allowed), will be rounded to the nearest even numbered penny.

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

• Math is one of my weaknesses and some of you seem good at it.  This is a routine I inherited and use in production.  It is to round from four places to two and should be "banker's rounding".  Is it flawed and if so which of the ones posted which would be "best"?

update #IntermediateBR set paidamount = ROUND(paidAmount, 2)

where convert(bigint, paidamount * 10000)%100 > 0

• Tyson, as I see your script is not using banker's rounding. ROUND - uses traditional arithmetic (5 always is rounded up).

If you are rounding only on DB level it's better to use STR function (see posts of Jeff Moden).

But if you also round in .NET code it's better to use BankRoundDotNet (posted by myself). It behaves equally to Math.Round in .NET (with the same bugs )

That's my humble opinion.

• Hey folks... I screwed up way back when and I've crossed out my posts about this because they were wrong... what I saw as Banker's Rounding was actually rounding of underlying FLOAT's... the STR function does NOT do Bankers' Rounding as I previously stated (Serqiy showed me about the FLOAT thing).

That's what I get for using such a narrow test range... seemed to work fine there but doesn't work in other places on the number line.

I know it's almost a year after these original posts, but I wanted to make sure that no one else was misled by the bad info I posted.  And my most sincere appologies to those folks who used that bad info.

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

• Another implementation that I just wrote:

```CREATE FUNCTION BANKROUND(@val as money)
RETURNS money AS
BEGIN
DECLARE @nonsig AS money
DECLARE @newval AS money
DECLARE @changesign AS bit
IF @val  0.005 OR (@nonsig = 0.005 AND @newval % 0.02 = 0.01)
SELECT @newval = @newval + 0.01
IF @changesign = 1
SELECT @newval = -@newval
RETURN @newval
END```

This only works for money data types at present, but it does avoid any floating point calculation and potential overflow issues with multiplying by 100. I think it could be modified to support decimal data rounded to @n decimal places as well by modifying the 0.005, 0.01 and 0.02 hard coded numbers to be variables 5 / 10^(@n+1), 1 / 10^@n, and 2 / 10^@n, respectively.

-- Jason Carter

• Thanks Carl...

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

• Carl,

Your RoundBanker does NOT implement Banker's Rounding correctly.

Because people seem to be using your implementation, I thought I should post this message to warn them.

The problem is that your understanding of Banker's Rounding is incorrect. It does NOT apply from right to left.

According to Wikipedia, these are the steps:

[font="Courier New"]1. Decide which is the last digit to keep.

2. Increase it by 1 if the next digit is 6 or more, or a 5 followed by one or more non-zero digits.

3. Leave it the same if the next digit is 4 or less

4. Otherwise, all that follows the last digit is a 5 and possibly trailing zeroes; then change the last digit to the nearest even digit. That is, increase the rounded digit if it is currently odd; leave it if it is already even.[/font]

In other words, the only case Banker's Rounding behaves differently from the Arithmetic Rounding is when the part to be rounded is exactly halfway: 0.00500000000... (if you are rounding to a penny).

0.005000001 must round to a penny: 0.01

SELECT dbo.RoundBanker(0.005000001, 2) returns 0.0, which is incorrect.

You wrote:

Banker's Round at 3 gives 3.455

Banker's Round at 2 gives 3.46

Second Pair:

Banker's Round at 3 gives 3.465

Banker's Round at 2 gives 3.46

3.4546 rounded to 2nd decimal must give 3.45

3.4653 rounded to 2nd decimal must give 3.47

• I started this thread 2 year ago. And it still seems to be actual.

For all this time I'm using the following funtions for banker's rounding and seems it work great:

[font="Courier New"]CREATE function dbo.BankRound (@val as numeric(30,15) )

returns money

as

begin

declare @factor int

set @factor = 100

declare @temp as double precision,

@fix_temp as double precision

set @temp = @val * @factor

set @fix_temp = floor(@temp + 0.5 * sign(@val))

if (@temp - cast(@temp as int) = 0.5)

begin

if ((@fix_temp / 2) <> cast(@fix_temp / 2 as int))

begin

set @fix_temp = @fix_temp - sign(@val)

end

end

return @fix_temp / @factor

end [/font]

• Wow :w00t: