T-SQL ROUND(decimal, int) vs C# Round(Decimal, Int32)

[Matt]

Anybody noticed that SQL Server rounds up if the value is half way
between two rounded values, but C#'s Decimal.Round(D ecimal,Int32)
rounds to nearest even number?
[color=blue]
>From MSDN: "When d is exactly halfway between two rounded values, the[/color]
result is the rounded value that has an even digit in the far right
decimal position. For example, when rounded to two decimals, the value
2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process is
known as rounding toward even, or rounding to nearest."

I perform the same calculation sometimes on the web server in C# and
sometimes at the database in T-SQL, but want to get the same result
from both calculations. Could anybody offer any strategies for dealing
with this?

Thanks ~ Matt

[gabe101]

Re: T-SQL ROUND(decimal, int) vs C# Round(Decimal, Int32)

I would be interested to hear what the veterans in this group have to
say about this one also. I use Report Builder for our reports and
forms and it uses Delphi Pascal. This variant of Pascal uses the
rounding to even also. Makes for interesting reports when SQL does one
side of the equation (like a subtotal + tax + shipping) and Pascal does
the grand total, it's quite possible to end up with 100.01 + 0 + 0 =
100.00 (SQL value + SQL value + SQL value = Pascal value)

Gabe

[Hugo Kornelis]

Re: T-SQL ROUND(decimal, int) vs C# Round(Decimal, Int32)

On 30 Jan 2006 08:42:43 -0800, Matt wrote:
[color=blue]
>Anybody noticed that SQL Server rounds up if the value is half way
>between two rounded values, but C#'s Decimal.Round(D ecimal,Int32)
>rounds to nearest even number?
>[color=green]
>>From MSDN: "When d is exactly halfway between two rounded values, the[/color]
>result is the rounded value that has an even digit in the far right
>decimal position. For example, when rounded to two decimals, the value
>2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process is
>known as rounding toward even, or rounding to nearest."
>
>I perform the same calculation sometimes on the web server in C# and
>sometimes at the database in T-SQL, but want to get the same result
>from both calculations. Could anybody offer any strategies for dealing
>with this?[/color]

Hi Matt,

This method of rounding is called "bankers rounding". Here's a link to a
previous discussion about it, including some techniques to do this in
SQL Server (warning - long URL, might wrap)

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/b47c6ab25aacb7da/85e9e40e0a7f6f32?lnk=st&q=bankers+rounding+sql+server&rnum=1&hl=en#85e9e40e0a7f6f32

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Re: T-SQL ROUND(decimal, int) vs C# Round(Decimal, Int32)

Matt (mattmorg55@gma il.com) writes:[color=blue]
> Anybody noticed that SQL Server rounds up if the value is half way
> between two rounded values, but C#'s Decimal.Round(D ecimal,Int32)
> rounds to nearest even number?
>[color=green]
>>From MSDN: "When d is exactly halfway between two rounded values, the[/color]
> result is the rounded value that has an even digit in the far right
> decimal position. For example, when rounded to two decimals, the value
> 2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process is
> known as rounding toward even, or rounding to nearest."[/color]

Unbelievable! I thought that this was just something that was taught
out of a whim for a while in Swedish schools when I was a kid. (Since
long replaced. A colleague who's 15 years younger than me was completely
baffled when she heard of the concept.)
[color=blue]
> I perform the same calculation sometimes on the web server in C# and
> sometimes at the database in T-SQL, but want to get the same result
> from both calculations. Could anybody offer any strategies for dealing
> with this?[/color]

Write your own rounding function in any of the languages. If you are on
SQL 2000, it be may be better for performance to do it in C#. If you are
on SQL 2005, you would still do it in C#, but you could use the function
in SQL Server.

In fact, we have done something of the kind, although our problem was
different. We needed to round floating-point numbers "intelligently" .

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx