Andrew Raymond


We have encountered something of a consistency issue in doing decimal type rounding between SQL Server 2005 and the .NET 2.0 runtime.

SQL Server seems to be using round to zero for Decimal and Money values. However, the .NET runtime, by default, uses round to even (also called 'Bankers Rounding'.) We are therefore stuck between a number of unpaletable options. We can:

  • Change every instance of rounding in our .NET codebase to use the overloaded rounding to force midpoint rounding.
  • Add a .NET extension to the database and call it in place of the built-in ROUND function.
  • Write our own banker's rounding function in T-SQL and call it.
  • Find a setting in either .NET or SQL Server to change the default rounding mode.

The first three all seem pretty nasty, though the middle two are probably the easiest within our (relatively speaking) C# heavy codebase. I seem to have pretty well eliminated finding a .NET setting (though I may have missed something.)

Is there an appropriate setting in SQL Server to do this As yet I have not found one.

Andrew Raymond
Mitchell 1
Andrew.Raymond@mitchell1.com





Re: Rounding Modes and .NET Runtime Consistency

KeWin


I don't believe there is any setting available to change the rounding scheme used in SQL Server, at least I can't remember ever having seen one...

/Kenneth