TSQL Rounding VS C# Rounding

Asked at 2017-01-11 13:48:03Z
  • 5 Subscribers
  • 114 Views
3

This problem has driven me crazy. I am using Microsoft SQLExpress 2016 to write a stored procedure. One of the requirement 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 C# produces 0.04? Shouldn't it be 0.05?

What should I do so that C# rounding = T-SQL rounding? Can anyone help me?

Thanks, Sam


Out of curiousity, 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

Can anyone explain this?

Thanks


2 answers in total

5
HoneyBadger Posted at 2017-01-11 13:51:25Z

This is because .NET defaults to 'ToEven' rounding, while SQL uses 'AwayFromZero'. See This. These are different rounding methods, 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, 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', it 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 it's 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 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.

2
mchomin Posted at 2017-01-11 13:56:59Z

C# allows you to specify what to do in the midpoint rounding situation - https://msdn.microsoft.com/en-us/library/ms131275(v=vs.110).aspx

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

Answer this questsion