Round

Question

Round

Viewing 7 posts - 16 through 22 (of 22 total)

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

sknox SSChampion Points: 12388 More actions · Answer 1

I think the explanation is just plain wrong.

I don't see anything which would produce data types or numbers with the precision and scale of (2,1) or (1,1) -- 0.999 is (3,3) and 2 is an int. The result, 1.00, is (3,2).

What's happening is that SQL Server is not adjusting the resultant datatype for the potential increase in number of digits to the left of the decimal point (i.e, a potential decrease in scale of 1)

The datatype for ROUND is based on the original type (3,3). But rounding up to two decimal places gives you a result of 1.00, which is (3,2). A number with precision and scale of (3,2) cannot fit in (3,3) because there is no place to put the additional number to the left of the decimal point.

This is supported by the fact that SELECT ROUND(CAST(0.999 AS NUMERIC(4,3)), 2) works fine, and that SELECT ROUND(9.999, 2) does not.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

September 14, 2015 at 7:11 am

#1826334

Tricky for me, thanks.

g.britton SSChampion Points: 13863 More actions · Answer 3

sknox (9/14/2015)
I think the explanation is just plain wrong.
I don't see anything which would produce data types or numbers with the precision and scale of (2,1) or (1,1) -- 0.999 is (3,3) and 2 is an int. The result, 1.00, is (3,2).
What's happening is that SQL Server is not adjusting the resultant datatype for the potential increase in number of digits to the left of the decimal point (i.e, a potential decrease in scale of 1)
The datatype for ROUND is based on the original type (3,3). But rounding up to two decimal places gives you a result of 1.00, which is (3,2). A number with precision and scale of (3,2) cannot fit in (3,3) because there is no place to put the additional number to the left of the decimal point.
This is supported by the fact that SELECT ROUND(CAST(0.999 AS NUMERIC(4,3)), 2) works fine, and that SELECT ROUND(9.999, 2) does not.

+1 quite right. the explanation is wrong in the details. (though correct in its intent I believe)

Gerald Britton, Pluralsight courses

Ken SSCoach Points: 17191 More actions · Answer 4

Interesting behavior for sure. Thanks for the question.

webrunner SSC-Dedicated Points: 32195 More actions · Answer 5

Thanks for the question and discussion. I was not aware of any of this.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

V strange

if you try any of the below, the results will be:

"Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type numeric."

SELECT round(.999,0);

SELECT round(.999,1);

SELECT round(.999,2);

but, if you type :

SELECT round(.999,3); and so on, you'll get results.

Whereas in Oracle,PostGre, you get the correct as expected results.

Any flaw by MS?

Thanks.

Eirikur Eiriksson SSC Guru Points: 182939 More actions · Answer 7

SQL-DBA-01 (9/14/2015)
V strange
if you try any of the below, the results will be:
"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type numeric."
SELECT round(.999,0);
SELECT round(.999,1);
SELECT round(.999,2);
but, if you type :
SELECT round(.999,3); and so on, you'll get results.
Whereas in Oracle,PostGre, you get the correct as expected results.
Any flaw by MS?

Not a flaw, the server interprets the literals as numeric(3,3), any attempt to implicitly lower the precision will error.

😎