# Rounding question

• Comments posted to this topic are about the item Rounding question

• 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.

Ron

• 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

• 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

• 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.

• 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:

• 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

_______________________________________________________________
http://www.sqlservercentral.com/articles/Best+Practices/61537/

• This was removed by the editor as SPAM

• 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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

• 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

@a

,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.

• 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

• 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

• This reference may help:

• 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:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

• Jeff Stratford (11/19/2012)

This reference may help:

Quoting from the link Jeff cited:

Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.

Note the "impossible"!

Viewing 15 posts - 1 through 15 (of 45 total)