Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Timothy Bates » Are all rounds created equal
Add to briefcase
19 posts, Page 1 of 2
12»»

Are all rounds created equal Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
timothy bates
Posted Tuesday, October 12, 2010 8:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 20, 2012 10:20 AM
Points: 197, Visits: 98
Comments posted to this topic are about the item Are all rounds created equal
Post #1003332
Oleg Netchaev
Posted Tuesday, October 12, 2010 9:04 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:39 PM
Points: 1,662, Visits: 1,708
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 to even rule
SQL Server uses away from zero rule.

Oleg
Post #1003334
Hardy21
Posted Tuesday, October 12, 2010 10:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:42 PM
Points: 877, Visits: 1,158
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 :)

Thanks
Post #1003352
Hugo Kornelis
Posted Wednesday, October 13, 2010 12:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 5,232, Visits: 7,022
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?)

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1003376
Mighty
Posted Wednesday, October 13, 2010 1:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:36 AM
Points: 2,716, Visits: 981
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.
Post #1003382
Hugo Kornelis
Posted Wednesday, October 13, 2010 1:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:24 AM
Points: 5,232, Visits: 7,022
Mighty (10/13/2010)
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.

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)

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1003394
paul.knibbs
Posted Wednesday, October 13, 2010 2:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:56 AM
Points: 1,256, Visits: 4,253
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!).
Post #1003403
Mighty
Posted Wednesday, October 13, 2010 2:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:36 AM
Points: 2,716, Visits: 981
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).

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.
Post #1003405
Toreador
Posted Wednesday, October 13, 2010 2:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 1,356, Visits: 4,761
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'.
Post #1003422
Rune Bivrin
Posted Wednesday, October 13, 2010 4:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:46 AM
Points: 2,655, Visits: 717
Hugo Kornelis (10/13/2010)

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)

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.

Just because you're right doesn't mean everybody else is wrong.
Post #1003458
« Prev Topic | Next Topic »

Add to briefcase
19 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.