CODEWITHSUNDEEP
c#sql-serverrounding
VJOY
VJOY

Reputation: 3792

Why C# round and SQL round functions yields different outputs?

I am using ROUND function from C# and SQL, and surprisingly both are yielding different results.

In SQL: ROUND(1250.00, -2) = 1300

In C# ROUND 1250 with round and precision = 2 = 1200

Has anyone came across this situation before?

Upvotes: 9

Views: 7970

Answers (4)

AgentFire
AgentFire

Reputation: 9780

Use the parameter on Math.Round, MidpointRounding, to specify the way to round your numbers.

public enum MidpointRounding
{
    // When a number is halfway between two others, it is rounded toward
    // the nearest even number.
    ToEven = 0,

    // When a number is halfway between two others, it is rounded toward
    // the nearest number that is away from zero.
    AwayFromZero = 1,
}

You can use it this way:

int presicion = 2;
double valueToRound;

Math.Round(valueToRound / Math.Pow(10, precision), MidpointRounding.AwayFromZero)
    * Math.Pow(10, precision);

Upvotes: 0

Baz1nga
Baz1nga

Reputation: 15579

if you read http://msdn.microsoft.com/en-us/library/wyk4d9cy.aspx, you'll see that the default rounding is "round to even" (banker's rounding) where as SQL Server appears to be using "standard" rounding

Update SQL Server Either does Symmetric Arithmetic Rounding or Symmetric Round Down (Fix) depending on arguments

how to solve the problem: Implement a custom rounding procedure: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q196652&ID=KB;EN-US;Q196652

Upvotes: 4

Keith Irwin
Keith Irwin

Reputation: 5668

The difficult part in rounding is what to do with 5's in decimal. They're actually exactly half-way, so they constitute a tie. There's a good bit about tie-breaking in wikipedia's Rounding article. Essentially, C# uses banker's rounding which rounds down when the last unrounded digit is even and up when it's odd. This is in line with the IEEE standard. SQL instead follows the "always round 5's up" rule.

Upvotes: 2

Tanzelax
Tanzelax

Reputation: 5714

C# uses banker's rounding by default, where when you're exactly on the .5 mark, it rounds to the nearest even number instead of always rounding up.

The remarks section of the msdn article describes this behavior. Basically it's to reduce rounding errors when you accumulate a lot of rounded numbers together.

Upvotes: 8

Related Questions