SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Timothy Bates / Are all rounds created equal / Latest Posts

RE: Are all rounds created equal

kalyani.k478 — Fri, 30 Nov 2012 02:16:47 GMT

+1:)

RE: Are all rounds created equal

SQL-DBA-01 — Mon, 01 Nov 2010 08:09:42 GMT

Nice one. I hope 2.50 and 2.5 or 3.60 and 3.6 are taken as same!

RE: Are all rounds created equal

sharath.chalamgari — Tue, 19 Oct 2010 22:46:55 GMT

Thanks! it really helped me to understand clearly

RE: Are all rounds created equal

Dhruvesh Shah — Tue, 19 Oct 2010 01:14:18 GMT

Awesome Question...

RE: Are all rounds created equal

SQLRNNR — Mon, 18 Oct 2010 10:17:37 GMT

Thanks for the QOTD

RE: Are all rounds created equal

UMG Developer — Wed, 13 Oct 2010 18:16:08 GMT

Thanks for the question.As a note I have always hated the round "to even" method that Microsoft sometimes uses.

RE: Are all rounds created equal

timothy bates — Wed, 13 Oct 2010 13:21:30 GMT

Thanks for the comments.In a view SQL Server returned n.n where in query analyzer it returned n.n0 for each.My main point was to showcase that there is disparity in programs as to how rounding is performed. Logic or specifications taken at face value (Round the average tax to the nearest tenth) can create differences in results. If you have an application performing the rounding and storing it in the db and run a periodic db script to verify you may get answers that result in 'random' discrepancies.TimThere are only 10 types of people that understand binary, those that do and those that do not.

RE: Are all rounds created equal

Oleg Netchaev — Wed, 13 Oct 2010 08:49:32 GMT

[quote][b]Daniel Bowlin (10/13/2010)[/b][hr]Interesting question. I thought all Microsoft software did banker's rounding. Thanks.[/quote]It is flexible. CLR procedures in SQL Server, any .NET code can use one of the two MidpointRounding enum values as a [b]mode[/b] parameter value of the of the Math.Round method:Math.Round(4.5, 0, MidpointRounding.AwayFromZero); -- 5Math.Round(4.5, 0, MidpointRounding.ToEven); -- 4Oleg

RE: Are all rounds created equal

Daniel Bowlin — Wed, 13 Oct 2010 06:46:59 GMT

Interesting question. I thought all Microsoft software did banker's rounding. Thanks.

RE: Are all rounds created equal

Rune Bivrin — Wed, 13 Oct 2010 04:21:42 GMT

[quote][b]Hugo Kornelis (10/13/2010)[/b][hr]It is your client software that converts this to a character representation so that you can see it on your screen. The client software chooses to represent these values with one trailing zero.(To check this, open an access project, create a pass-through query to execute this query on SQL Server, and heck the results. They'll show as 2.5 and 3.6. Or in my case as 2,5 and 3,6, since I have a Dutch version of Access, and the Dutch, like many other Europeans, use a decimal comma rather than a decimal point)[/quote]I'd just like to point out that it isn't the version of Access that determines what decimal separator you get, it's the regional settings in the Control Panel.

RE: Are all rounds created equal

Toreador — Wed, 13 Oct 2010 02:32:43 GMT

I got this right because we've been bitten by the same thing with VB6, which also uses bankers rounding. We had to write our own rounding method to get it to round 'properly'.

RE: Are all rounds created equal

Mighty — Wed, 13 Oct 2010 02:03:25 GMT

[quote]Wrong!SQL Server will return a result set that consists of two columns (both datatype numeric(3,2)), and one row. The values in this row are 2.5 and 3.6. (Or 2.50 and 23.60, or 0002.500000 and 03.6000 - those are all different representations of the same numeric value).[/quote]I don't agree with what you say. For me it's not about what is shown on the screen, but what is returned. SQL Server returns a decimal(3, 2), as you stated. So it will not store 2.5 or 2.500, which have a different precision, but exactly 2.50.

RE: Are all rounds created equal

paul.knibbs — Wed, 13 Oct 2010 02:01:13 GMT

I had a suspicion the answer would be different, so I looked up the Access ROUND() function--didn't realise it did round-to-even before! At school I'm pretty sure we were always taught to round up (e.g. rounding -2.45 to 1 decimal place would become -2.4), though. (That was the best part of 30 years ago and my memory may be faulty on that point, though!).

RE: Are all rounds created equal

Hugo Kornelis — Wed, 13 Oct 2010 01:40:47 GMT

[quote][b]Mighty (10/13/2010)[/b][hr]My opinion is that none of the answers is correct.SQL Server will return 2.50 and 3.60, not 2.5 and 3.6.[/quote]Wrong!SQL Server will return a result set that consists of two columns (both datatype numeric(3,2)), and one row. The values in this row are 2.5 and 3.6. (Or 2.50 and 23.60, or 0002.500000 and 03.6000 - those are all different representations of the same numeric value).It is your client software that converts this to a character representation so that you can see it on your screen. The client software chooses to represent these values with one trailing zero.(To check this, open an access project, create a pass-through query to execute this query on SQL Server, and heck the results. They'll show as 2.5 and 3.6. Or in my case as 2,5 and 3,6, since I have a Dutch version of Access, and the Dutch, like many other Europeans, use a decimal comma rather than a decimal point)

RE: Are all rounds created equal

Mighty — Wed, 13 Oct 2010 01:06:51 GMT

My opinion is that none of the answers is correct.SQL Server will return 2.50 and 3.60, not 2.5 and 3.6.

RE: Are all rounds created equal

Hugo Kornelis — Wed, 13 Oct 2010 00:54:45 GMT

Thanks for a good question!Here in the Netherlands, only one type of rounding is taught in school. The type that Wikipedia calls "away from zero" - you only check the first digit to be discarded, for 0-4 (truncate), or 5-9 (add one to previous digit).I was aware of "bankers rounding" (or "round to even") through questions in forums and usenet. But until I followed the Wikipedia link in the documentation, I did not klnow that there are so many other ways of rounding.Thanks!(BTW, did you also file a documentation bug because the SQL Server documentation doesn't specify its tie-breaking rule?)

RE: Are all rounds created equal

Hardy21 — Tue, 12 Oct 2010 22:56:46 GMT

Though I got it right, its nice to see difference between Access & SQL server in rounding the number. Thanks for the question, I remembered my old days when we worked in Access :)

RE: Are all rounds created equal

Oleg Netchaev — Tue, 12 Oct 2010 21:04:55 GMT

This is an excellent question, kinda reminded me of good ol' days when I worked with Access :-), thank you Timothy. The difference between Access and SQL Server is caused by the difference in so called mid point rounding rule:Access uses[b] to even [/b]ruleSQL Server uses [b]away from zero[/b] rule.Oleg

Are all rounds created equal

timothy bates — Tue, 12 Oct 2010 20:57:52 GMT

Comments posted to this topic are about the item [B]Are all rounds created equal[/B]