By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,364 Members | 1,580 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,364 IT Pros & Developers. It's quick & easy.

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

P: n/a
Anybody noticed that SQL Server rounds up if the value is half way
between two rounded values, but C#'s Decimal.Round(Decimal,Int32)
rounds to nearest even number?
From MSDN: "When d is exactly halfway between two rounded values, the

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

Jan 30 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Jan 30 '06 #2

P: n/a
On 30 Jan 2006 08:42:43 -0800, Matt wrote:
Anybody noticed that SQL Server rounds up if the value is half way
between two rounded values, but C#'s Decimal.Round(Decimal,Int32)
rounds to nearest even number?
From MSDN: "When d is exactly halfway between two rounded values, the

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?


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/micro...e9e40e0a7f6f32

--
Hugo Kornelis, SQL Server MVP
Jan 30 '06 #3

P: n/a
Matt (ma********@gmail.com) writes:
Anybody noticed that SQL Server rounds up if the value is half way
between two rounded values, but C#'s Decimal.Round(Decimal,Int32)
rounds to nearest even number?
From MSDN: "When d is exactly halfway between two rounded values, the 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."


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.)
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?


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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 30 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.