Bankers Rounding

[bitsmith2k]

Does anyone know of a way to perform bankers rounding using sql server 7?

any resources would be appreciated..

thanks

mike

 

[SQLBill]

What is 'bankers rounding'?

-SQLBill

 

[bitsmith2k]

well in the data that i'm looking at i have:
taxttoal: 0.24
the formula for getting this is:
((netprice / quantity)(percentage))(quantity)
so
((3.5 / 1) (0.07))(1) = 0.245

When the data was inserted into the table (from VB),
VB rounded it to 0.24 (using the Round() function).

SQL Server rounds the same number to (what i'd expect) 0.25.

I discovered that VB uses Banker's Rounding to get that value.

Just wondering if there was a way to do the same thing on the SQL side of things so my totals add up properly.

mike

 

[SQLBill]

So, what is banker's rounding? Does it truncate the last digit:

0.245 becomes 0.24
0.241 becomes 0.24
0.247 becomes 0.24

You could just truncate the data before inputting it.

or is it the same as regular rounding except that 5 is rounded down instead of up?

You would probably have to write a trigger or other kind of script to handle that.

-SQLBill

 

[bitsmith2k]

sorry i wasn't more clear.. i don't function too well right before lunch

.243 = .24
.245 = .24
.247 = .25

 

[SQLBill]

I've been looking through the Books OnLine plus the Guru's Guide to Transact-SQL and can't find a function that does what you want. You might consider creating a trigger that will round down the number when it ends in a 5.

-SQLBill

 

[bitsmith2k]

it gets a little trickier..
i should also add this:
.235 = .24
.255 = .26
and .245 = .24

 

[SQLSister]

Why would only .245 round down but .255 and .235 round up?
is it based on odd and even in the digit before the rounding? WOuld .265 round to .26?

 

[bitsmith2k]

ya.. in vb6 .265 rounds to .26