T-SQL rounding vs. C# rounding

Question

I am using Microsoft SQL Server Express 2016 to write a stored procedure. One of the requirements is to do rounding. But every now and then, the rounding is wrong. I found out that T-SQL rounding is not exactly the same with C#, but why?

Compare two rounding below:

In T-SQL: ROUND(0.045, 2) --> this will produce 0.05

In C#: Math.Round(0.045, 2) --> this will produce 0.04

Why does C# produce 0.04? Shouldn't it be 0.05?

What should I do so that C# rounding = T-SQL rounding?

---

Out of curiosity, I tried this in C#:

Math.Round(0.055, 2)

Guess what C# rounded it to? It rounded to 0.06! Now, I am completely confused!

Math.Round(0.045, 2)   // This becomes 0.04
Math.Round(0.055, 2)   // This becomes 0.06

What is the explanation?

Jeffers

Possible duplicate of why-does-math-round2-5-return-2-instead-of-3. Answer by Jon Skeet himself ;) – Ellisellison

Answer 1

This is because .NET defaults to 'ToEven' rounding, while SQL uses 'AwayFromZero'. See this. These are different rounding methods, and they differ in how they treat 5.

AwayFromZero rounds it up to the next positive, or down to the next negative number. So, 0.5 becomes 1, -0.5 becomes -1. ToEven rounds to the nearest even number. So 2.5 becomes 2, 3.5 becomes 4 (and likewise for negative numbers). Numbers other than 5 are treated the same, and they are rounded to the nearest number. Since 5 is equidistant from two numbers, it's a special case, with different strategies.

ToEven is also known as 'Banking Rules', and it's the default used in IEEE_754, which is why it's the default in .NET.

Conversely, AwayFromZero is also known as 'Commercial Rounding'. I don't know why it is the default of SQL Server, probably simply because it's the most widely known and understood method.

Of course, you can always configure what you need:

In C# you can do:

Math.Round(value, MidpointRounding.ToEven)

or

Math.Round(value, MidpointRounding.AwayFromZero)

In SQL you can use ROUND(), FLOOR() and/or CEILING().

Which of the methods is better, depends what you use it for, and what you want. For reasonable collections/distributions, the average of rounded toEven values is the same as its original values. This is not necessarily the case with AwayFromZero. If you have a collection with many .5 data, rounding AwayFromZero will treat all those values the same, and introduce a bias.

The effect is that the average of the rounded values is not the same as the average of the original values. The point of rounding is making a value simpler, while it holds the same meaning. This is no longer the case if the averages don't match; the rounded values have a (slightly?) different meaning then the original values.

Jacquejacquelin

I was writing the same answer, with a different link: msdn.microsoft.com/en-us/library/… so +1 for your speed sir – Thomson

Great answer! +1 I added an addendum via another answer as a compliment to this one, showing another approach to performing this type of rounding in T-SQL :-) – Elongation

use the tsql round it is much faster. Any time you have use a dot.net function it will be signficantly slower – Vanlandingham

Answer 2

C# allows you to specify what to do in the midpoint rounding situation - see Round(Decimal, Int32, MidpointRounding).

Math.Round(0.345, 2, MidpointRounding.AwayFromZero); // returns 0.35

Schulze

Answer 3

Adding to HoneyBadger's answer, you can use SQLCLR (as of SQL Server 2005) to expose the .NET Math.Round() method to T-SQL so that it can be used in queries.

You can either code this yourself, or you can simply download the Free version of the SQL# SQLCLR library (which I created, and contains both Math_RoundToEvenFloat and Math_RoundToEvenDecimal in the Free version), and then do:

SELECT ROUND(0.045, 2), SQL#.Math_RoundToEvenFloat(0.045, 2);
-- 0.050    0.04

SELECT ROUND(0.055, 2), SQL#.Math_RoundToEvenFloat(0.055, 2);
-- 0.060    0.06

There are both "Float" and "Decimal" -specific functions for performance and accuracy reasons. FLOAT values transfer between T-SQL and CLR contexts much faster, but can sometimes contain an extra 0.000000000005 (or something like that) coming into the CLR code, so be sure to use the function that matches the datatype you are using. If you are doing financial calculations, then you should already be using DECIMAL (a precise datatype). If you are using FLOAT (an imprecise datatype) for financial calculations, you should really change that to DECIMAL sooner than later ;-).

Elongation