Just making sure of a nuance to Banker's Rounding... it implies rounding to the nearest penny (hundreths) no matter how many decimal places may be involved... is that correct? (I know... a bit silly on my part but I just want to be 100% sure.)
Does the function you posted work as described in the code or is there a problem with it... just trying to figure out where THIS thread is gonna go
It can denote rounding to the nearest penny, but it's not really a banking specific function (it's actually not widely used by banks as far as I know, but far more often in applications like digital signal processing, etc.), so it can also round to the nearest thousandths, ten thousandths, even dollars, for that matter.
The only difference between it and traditional rounding is the handling of values exactly halfway between two possible results, so if rounding to the nearest penny, banker's rounding would kick in with given a given value of 1.045, but not 1.044999999999999, nor 1.0450000000000001. It has to be exactly halfway, otherwise, just use the Round function and you should get the same results.
There is another difference, but it's more implementational than a directive of the function. Many of the functions available allow you to round things to the nearest nickel (values not denoted by a single decimal position), for instance, which isn't an intended purpose of traditional rounding, even though it's possible to do so. I think that is probably due to the name of the function as much as anything, although with the push to nickels (and their equivalents) being the smallest currency in many places, it has gained some traction.
Ok... thanks David...
And I agree... Heh... after the last thread about this, I started asking all my CPA "friends" about it... out of 18 CPA's, only 2 of them even knew what Banker's Rounding was (none of them knew "round to even"), only 1 of them described it absolutely correctly, and none of them had ever used it except in class somewhere And all of them use traditional rounding to the nearest dollar when preping taxes for folks and corporations because Uncle Sam expects it that way... even if they have a computer program do it for them Also talked with some banking "friends"... similar results. So, I tend to agree... "not really a banking specific function".
That brings me to my next set of questions for anyone that cares to answer... (not directed at anyone in particular so no one get mad, please) with that in mind... and with the idea that it's more likely that Banker's Rounding will be used in "Digital Signal Processing" (although I don't see a sign of it in the Fast Fourier Transformation algorithyms on Wikipedia), can anyone think of a reason to actually have a Banker's Rounding function in SQL? What would we use it for? Perhaps in a more scientific oriented database (I tend to think of telephone call records and "banking functions" just 'cause I'm in a non-scientific area)? And, even if we did use it in the non-scientific world of call details and/or money, would a decent SOX auditor be capable of doing the rounding correctly? Would (s)he even know what the heck it is?
Or, is it all just "case in point" and a "whole lot of fun to figure/comiserate about" No, I'm not trying to be a smart guy about this... I'm just thinking that it's such a rarely used form of rounding that most folks just don't even know what it is and I'm really curious if anyone actually uses it in SQL Server (or any RDBMS for that matter)...
Jeff, it's actually a bit more commonly used than the anecdotal evidence would lead you to suggest, it's just poorly named. To be fair, it also has a ton of different names it goes by, which makes it more confusing. "Banker's Rounding", "Unbiased Rounding", "Statistician's Rounding", "Convergent Rounding", "Round To Even", "Round Half Even", "Round to Nearest", etc. On the other hand, it's also the rounding method used by millions of .NET apps, whether the users know it or not, since that is how Math.Round worked until 2005 (it still works that way, but now you can use traditional rounding if you'd prefer). It's the default rounding method per IEEE 754 (this is probably the biggest reason), .NET, several of the ASTM committees, Delphi, and many others. It's available in almost every programming language, Mathematica, Matlab, Excel, and countless other apps. In fact, SQL Server is one of the only places where I've needed it, yet it didn't exist natively. On the other hand, it wouldn't surprise me if Katmai supported it.
I've probably used it on only about 10% of the projects I've consulted on, and even then, I don't think all of them needed it (two of them were just "I heard this is good, so use it" type situations), but for those who do, it would be nice to have a working function. Basically, it's a tool. It could be indispensible to one person, and never picked up by another. I just think it would be nice for those who need it to have a good quality tool.
So, to answer your question, it's not universally used, but it's far more common than you'd think, so yes, I do think it's a wise component to have around.