Rounding Rules

Question

Post reply

Rounding Rules

Steve Jones - SSC Editor

SSC Guru

Points: 734459
More actions
August 22, 2016 at 9:05 pm

#77434

Comments posted to this topic are about the item Rounding Rules
August 23, 2016 at 12:08 am

This was removed by the editor as SPAM

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

You must be logged in to reply to this topic. Login to reply

Rune Bivrin SSCrazy Eights Points: 8441 More actions · Answer 1

And once again, Steve's proof-reading skills prove somewhat lackadaisical 😛

Just because you're right doesn't mean everybody else is wrong.

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 2

Yep - I knew the "right" answer, but I entered "An error" just because there was one - in the question itself! Again! Sorry Steve, as much as I respect your experience and knowledge on SQL Server, this isn't the first time a typo has made a nonsense of QotD. Please let's have some better quality proof reading.

funbi SSCarpal Tunnel Points: 4992 More actions · Answer 3

Was the first example 0.3 meant to make us think there would be some other result than 1.5?

RossOverThere SSC Veteran Points: 293 More actions · Answer 4

The example gave away the answer, surely? A good idea to point out that ROUND doesn't necessarily round to the nearest integer, but ROUND(0.3, 1) being 0.3 just tells you that ROUND(1.5, 1) = 1.5.

Or is it 1.7? 😛

Toni-256719 SSCertifiable Points: 5816 More actions · Answer 5

Toni-256719

SSCertifiable

Points: 5816

August 23, 2016 at 2:43 am

#1896827

I think Steve just has too much on his plate.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 6

The only thing that make me look twice was the 1.7 versus the 1.5. Then again, it being simple made me look a third time because I was looking for the trick. I guess I'd already found it. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

August 23, 2016 at 6:50 am

#1896877

Easy one. I actually use this once in a while.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 8

Arrgghh, sorry. Testing code in different ways and edited something without changing it back.

Truckin Old Hand Points: 310 More actions · Answer 9

The real take away is everyone makes mistakes, even sometimes when checking the work. Check, double check, take a break (and then triple check <if it is important>) and when prepared to live with the consequences, act.

D

🙂

George Vobr SSCrazy Eights Points: 9917 More actions · Answer 10

Thanks Steve for this question. It wasn't quite simple...:-)

An interesting problem of differences in the rounding of the data type float

vs. numeric is possible to see if you run this code:

DECLARE @i FLOAT = 1.055, @j-2 FLOAT = 1.555, @k FLOAT = 1.155;

SELECT @i as i_float, @j-2 as j_float, @k as k_float;

SELECT ROUND(@i, 2) as i_Rnd, ROUND(@j, 2) as j_Rnd, ROUND(@k, 2) as k_Rnd;

DECLARE @x NUMERIC(5, 3) = 1.055, @y NUMERIC(5, 3) = 1.555, @z NUMERIC(5, 3) = 1.155;

SELECT @x as x_numeric, @y as y_numeric, @z as z_numeric;

SELECT ROUND(@x, 2) as x_Rnd, ROUND(@y, 2) as y_Rnd, ROUND(@z, 2) as z_Rnd;

GO

Results:

--------

i_float j_float k_float

---------------------- ---------------------- ----------------------

1,055 1,555 1,155

(1 row(s) affected)

i_Rnd j_Rnd k_Rnd

---------------------- ---------------------- ----------------------

1,05 1,55 1,16

(1 row(s) affected)

x_numeric y_numeric z_numeric

--------------------------------------- --------------------------------------- ---------------------------------------

1.055 1.555 1.155

(1 row(s) affected)

x_Rnd y_Rnd z_Rnd

--------------------------------------- --------------------------------------- ---------------------------------------

1.060 1.560 1.160

(1 row(s) affected)

The value of @k is rounded correctly. If the number of the type float does not begin

behind the decimal point with 0 or 5, rounding is correct.

An explanation can perhaps find in the first sentence of the first paragraph of article

in the MSDN documentation for float and real (Transact-SQL): "Approximate-number data

types for use with floating point numeric data. Floating point data is approximate;

therefore, not all values in the data type range can be represented exactly."

See: https://msdn.microsoft.com/en-us/library/ms173773.aspx

Kevin Gill SSCrazy Points: 2348 More actions · Answer 11

Ah OK so I take it the question is still wrong and I haven't just lost my mind... As it's written now the "correct" answer is 1.7 but there is no option to answer 1.7.

I thought I was learning something totally new there.

-------------------------------Oh no!

djj SSCoach Points: 18831 More actions · Answer 12

Kevin Gill (8/23/2016)
Ah OK so I take it the question is still wrong and I haven't just lost my mind... As it's written now the "correct" answer is 1.7 but there is no option to answer 1.7.
I thought I was learning something totally new there.

Exactly what I thought.

Revenant SSC-Forever Points: 42467 More actions · Answer 13

These small mistakes generate interesting discussions. 🙂

Thanks, Steve!