Rounding Decimals using .NET and T-SQL
14 Jan 2010
asp.net bankers rounding c# mvc sql server
Had a perplexing problem whereby I was trying to round several decimal values stored in a SQL 2005 database. In several places I was rounding the values using T-SQL and then passing them to my .NET application. In other places I was passing the raw values to my .NET application and rounding in C#. The issue being that the rounded values differed depending on whether T-SQL or .NET was doing the rounding.
You will notice below that the inbuilt T-SQL rounding function will always round up when the third decimal digit is 5 or more.
However when you try and round the same values using the inbuilt .NET function strange things start to happen. As you can see in the example below, one of the values is rounded down and the other is rounded up when using the default implementation.
This is because the .NET function employs "Bankers Rounding" logic. This logic is alternatively referred to as "round half to even". Click here for more details...
There are two ways to compensate for this.
You can change the logic used by the .NET function by specifying a third parameter in your function calls. By setting the rounding type to MidpointRounding.AwayFromZero the function will match the logic used by the T-SQL function.
Alternatively you can create your own user defined "Bankers Rounding" T-SQL function. This will match the logic used by the default .NET function.
T-SQL function courtesy of Less Than Dot - Blog - SQL Server Rounding Methods.