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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Granting access to Microsoft SQL Server 17 25
Database connection opened on a machine 8 28
Report Builder 9 24
Mssql SQL query 14 24
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

772 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

10 Experts available now in Live!

Get 1:1 Help Now