Banker's Rounding in SQL Server 2000

Posted on 2004-10-18
Last Modified: 2008-01-16
How do I use Banker's Rounding in SQL Server 2000.  I tried to convert the function I found ( but was unable to make it work. Is there a built in function or a UDF someone has used in the past?


Question by:SpreadTheWord
LVL 32

Accepted Solution

bhess1 earned 500 total points
ID: 12343376
Mmmmm.... try this one

Create Function dbo.udf_BankerRound(@Amt as Money)
   Declare @Result Money
   Declare @Sgn smallint

   IF @Amt = Floor(@Amt)
      Set @Result = @Amt
      Set @Sgn = Sign(Ceiling(@Amt) - 2 * @Amt + Floor(@Amt))
      If @Sgn = 1
         Set @Result = Floor(@Amt)
         If @Sgn = 0
             Set @Result = 2 * Round(@amt / 2, 0)
             Set @Result = Ceiling(@Amt)

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Full or Partial Cache in Lookup Transformation in SSIS 16 46
SQL Query 17 30
MS SQL Inner Join - Multiple Join Parameters 2 17
Help Required 2 0
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now