Log in  ::  Register  ::  Not logged in

## Rounding question

 Author Message charlie-514368 SSC Journeyman Group: General Forum Members Points: 80 Visits: 766 Comments posted to this topic are about the item Rounding question bitbucket-25253 SSCrazy Eights Group: General Forum Members Points: 9373 Visits: 25280 Unfortunately the cited reference for the correct answers does NOT or I could not find the "tie breaking rule" in that reference. Any one have better luck ? If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read mickyT SSCommitted Group: General Forum Members Points: 1832 Visits: 3317 bitbucket-25253 (11/18/2012)Unfortunately the cited reference for the correct answers does NOT or I could not find the "tie breaking rule" in that reference. Any one have better luck ?Nope ... I suspect the difference in the results is rather with the floating point for x being slightly less than 3.65. Needless to say, I got it wrong ... should have looked for the trick.I would say without running this, there would be no way to accurately predict the answer Koen Verbeeck SSC-Dedicated Group: General Forum Members Points: 34071 Visits: 13270 It would be great to know more about this "tie-breaking rule", otherwise this question is just guess work. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?My blog at SQLKover.MCSE Business Intelligence - Microsoft Data Platform MVP bochambers SSC-Enthusiastic Group: General Forum Members Points: 141 Visits: 82 I initially thought that this was an issue of the approximate storage of the FLOAT data type such that the 3.65 was actually being stored as 3.649999999... for example but when using an exact numeric the exact same outcome occurs, e.g.`declare @x DECIMAL(3,2), @y DECIMAL(3,2)set @x = 3.65set @y = 3.75select 'x = ' + str(@x,10,1) select 'y = ' + str(@y,10,1)`Similarly if the datatype of @x is set too DECIMAL(2,1) then @x is stored as 3.6 not 3.7 whilst @y is stored as 3.8 as expected.I agree that more information should be provided on this rounding behaviour as this is not an expected behaviour and differs from the behaviour of ROUND(@x,1) which correctly rounds up to 3.7 as expected. mickyT SSCommitted Group: General Forum Members Points: 1832 Visits: 3317 bochambers (11/18/2012)I initially thought that this was an issue of the approximate storage of the FLOAT data type such that the 3.65 was actually being stored as 3.649999999... for example but when using an exact numeric the exact same outcome occurs, e.g.`declare @x DECIMAL(3,2), @y DECIMAL(3,2)set @x = 3.65set @y = 3.75select 'x = ' + str(@x,10,1) select 'y = ' + str(@y,10,1)`Similarly if the datatype of @x is set too DECIMAL(2,1) then @x is stored as 3.6 not 3.7 whilst @y is stored as 3.8 as expected.I agree that more information should be provided on this rounding behaviour as this is not an expected behaviour and differs from the behaviour of ROUND(@x,1) which correctly rounds up to 3.7 as expected.STR() may be changing the data type to float prior to conversion kapil_kk Hall of Fame Group: General Forum Members Points: 3578 Visits: 2766 I think this question is more about guessing rather than tricky one...select STR(4.65,10,1) -- it will gives 4.7select STR(3.65,10,1) -- it will gives 3.6 _______________________________________________________________To get quick answer follow this link:http://www.sqlservercentral.com/articles/Best+Practices/61537/ Stewart "Arturius" Campbell SSChampion Group: General Forum Members Points: 10863 Visits: 7319 This is an interesting question. The rounding issue here was quite unanticipated. ____________________________________________Space, the final frontier? not any more...All limits henceforth are self-imposed.“libera tute vulgaris ex” Hugo Kornelis SSChampion Group: General Forum Members Points: 13081 Visits: 12143 I'm not really happy with the question, and the explanation is just wrong.When I saw the question, I knew it was going to be related to inaccuracy in the floating point represtentation. So when I saw the answers, I immediately knew to choose the one that made the least logical sense - that's always the case with these kind of questions. Luckily, there was only one illogical answer. (If "3.7/3.7" had been offered as well, I would have had no choice but to run the query, as these answers are impossible to predict unless you are willing to manually convert it to a binary floating point representation in 53 (I think) bits.I like the answer even less. There are no tie-breaking rules used. If there were, then the answer should have included a reference to a page where these rules are actually documented, and this is defintely not the case. The specific answers returned by this single query may seem to suggest that STR() uses "bankers rounding", but that really is a mere coincidence. Many other values will round differently, as several posters in this topic already have explained.(By the way, I did some research and found that there has been a discussion on perceived bankers rounding by STR on this very forum - check http://www.sqlservercentral.com/Forums/Topic246556-8-1.aspx). Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis martin.whitton Ten Centuries Group: General Forum Members Points: 1432 Visits: 1774 Just out of interest, I tested the rounding of every float value in the sequence 0.65, 1.65, 2.65, etc up to 99.65 using the following code:`declare @a float ,@x tinyint;declare @tbl table ( col1 tinyint ,col2 decimal(4,1) );select @x=0;while @x<=100begin select @a=@x+0.65; insert into @tbl select @a ,round(@a,1); select @x=@x+1;end;select col1 ,col2from @tbl;`I found that 51 rounded down and 49 rounded up, which sounds fairly random except that every number from 4.65 to 15.65 rounds up, as does every number from 64.65 to 99.65. There must be some rule that governs this, but I think the best lesson is to always treat float values with caution.