November 18, 2012 at 2:26 am
Comments posted to this topic are about the item Rounding question
November 18, 2012 at 6:03 am
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 ?
November 18, 2012 at 11:26 am
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
November 18, 2012 at 3:08 pm
It would be great to know more about this "tie-breaking rule", otherwise this question is just guess work.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2012 at 6:34 pm
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.65
set @y = 3.75
select '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.
November 18, 2012 at 8:11 pm
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.65
set @y = 3.75
select '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:crazy:
November 18, 2012 at 11:29 pm
I think this question is more about guessing rather than tricky one...
select STR(4.65,10,1) -- it will gives 4.7
select 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/
November 19, 2012 at 12:30 am
This was removed by the editor as SPAM
November 19, 2012 at 1:10 am
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).
November 19, 2012 at 1:32 am
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<=100
begin
select
@a=@x+0.65;
insert into
@tbl
select
,round(@a,1);
select
@x=@x+1;
end;
select
col1
,col2
from
@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.
November 19, 2012 at 1:42 am
I think it depends on "inaccuracy in the floating point represtentation" as said Hugo.
STR convert any number to float.
Here the script to force the right round (a double cast to decimal):
declare @x float, @y float
set @x = '3.65'
set @y = '3.75'
--What values are returned by the following statements?
select str(CAST(CAST(@x as decimal(10,4)) as decimal(10,1)),10,1),@x
select str(CAST(CAST(@y as decimal(10,4)) as decimal(10,1)),10,1),@y
November 19, 2012 at 2:05 am
I got this question right simply because I have seen it before, but it is an illogical answer and there is no explanation for it. I agree with others in this discussion who have said that seeing the so-called "tie-breaking rule" would help clarify and - more importantly - help people understand this behaviour.
Nick
November 19, 2012 at 2:12 am
November 19, 2012 at 2:15 am
I agree with other posters on this thread that this is a badly-executed question. Picking two arbitrary values with inconsistent results without explaining the rules governing the rounding by STR seems like an incomplete QoTD.
That said, I wasn't aware of this inconsistent behaviour by STR so it's something new learned, I suppose.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
November 19, 2012 at 2:21 am
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply