Round up or down IV

Question

Post reply

Round up or down IV

Hugo Kornelis

SSC Guru

Points: 64790
More actions
August 23, 2010 at 9:25 pm

#225175

Comments posted to this topic are about the item Round up or down IV
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/

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

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

hrvoje.piasevoli Ten Centuries Points: 1377 More actions · Answer 1

Hi great qod!

Here's a tip:

Move the decimal point to the left by the negative number and then do a regular ROUND(d, 0). Applied to this it ends looking like ROUND(0.1666666,0) and there you have it.

I wish I'd come up with it before I answered incorrectly 🙂

Regards,

Hrvoje Piasevoli

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 2

Again good question on same round function.Thanks hugo

Malleswarareddy
I.T.Analyst
MCITP(70-451)

ziangij SSCrazy Eights Points: 8066 More actions · Answer 3

plz. refer http://technet.microsoft.com/en-us/library/ms175003.aspx

for

select ROUND(748.58, -3)

result displayed is 1000.00

but when i execute the same i get an error:

An error occurred while executing batch. Error message is: Arithmetic Overflow.

any suggestions plz. ?

tommyh SSCertifiable Points: 6302 More actions · Answer 4

ziangij (8/23/2010)
plz. refer http://technet.microsoft.com/en-us/library/ms175003.aspx
for
select ROUND(748.58, -3)
result displayed is 1000.00
but when i execute the same i get an error:
An error occurred while executing batch. Error message is: Arithmetic Overflow.
any suggestions plz. ?

Something with your connection options maybe. Are you running the default settings?

The 748.58 is treated by SQL as Numeric(5,2) so 1000 doesnt fit there. With every setting at the default however SQL allows this. Now i havent been able to replicate the error. But you can check out

http://www.eggheadcafe.com/software/aspnet/36185242/simple-round-call-results-in-arithmetic-overflow.aspx

and maybe get a tip or 2.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Again, great question and great explanation.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

ziangij (8/23/2010)
plz. refer http://technet.microsoft.com/en-us/library/ms175003.aspx
for
select ROUND(748.58, -3)
result displayed is 1000.00
but when i execute the same i get an error:
An error occurred while executing batch. Error message is: Arithmetic Overflow.
any suggestions plz. ?

I get the same error. The explanation is simple, and already given by tommyh: "748.58" is considered to be numeric(5,2), and the result of ROUND is the same data type as its input. But 1000.00 does not fit in a numeric(5,2).

The fix is simple - use an explicit CAST to get a data type that does have some room for expansion:

SELECT ROUND(CAST(748.58 AS decimal(6,2)), -3);

tommyh (8/24/2010)
With every setting at the default however SQL allows this. Now i havent been able to replicate the error.

Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?

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/

tommyh SSCertifiable Points: 6302 More actions · Answer 7

Hugo Kornelis (8/24/2010)

ziangij (8/23/2010)
tommyh (8/24/2010)
With every setting at the default however SQL allows this. Now i havent been able to replicate the error.
Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?

8.00.760SP3Standard Edition

9.00.1399.06RTMDeveloper Edition

Though i dont think it matters. Since 99.9% off all the code i write is for SQL2000 (yeah old). I use the old Query Analyzer. If i try the same in SSMS (2005) i get the error (with same options set in both programs). So not a SQL version issue but an issue with the program issuing the commands it seams.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

tommyh (8/24/2010)

Hugo Kornelis (8/24/2010)
ziangij (8/23/2010)
tommyh (8/24/2010)
With every setting at the default however SQL allows this. Now i havent been able to replicate the error.
Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?
8.00.760SP3Standard Edition
9.00.1399.06RTMDeveloper Edition
Though i dont think it matters. Since 99.9% off all the code i write is for SQL2000 (yeah old). I use the old Query Analyzer. If i try the same in SSMS (2005) i get the error (with same options set in both programs). So not a SQL version issue but an issue with the program issuing the commands it seams.

I don't have SA, but I tried using osql.exe and I also got 1000.00 as the result.

I consider this a bug. The error you get in SSMS is actually the correct and expected behaviour. But somehow, for some weird reason, the evaluation of ROUND(748.58, -3) itself does not result in an error; instead the value of 1000.00 is "somehow" (don't ask me how!) encoded in the space available for a decimal(5,2). This is error number one.

Error number two is client-specific. Apparently, QA and osql.exe will happily display a value if the server tells them to display the value 1000.00 with three positions before and two positions after the decimal place. SSMS behaves better - it knows this is impossible, and tells us about that.

Here is an interesting experiment (works on every client):

SELECT ROUND(789.98,-3) AS WeirdCol

INTO WeirdTable;

go

EXEC sp_help 'WeirdTable';

go

UPDATE WeirdTable

SET WeirdCol = WeirdCol - 0.001;

go

DROP TABLE WeirdTable;

go

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/

paul.knibbs SSCoach Points: 15320 More actions · Answer 9

Didn't know ROUND() could do this, but once I looked it up in Books Online it seemed fairly straightforward--I learned something today, which is always nice!

hrvoje.piasevoli Ten Centuries Points: 1377 More actions · Answer 10

Hugo Kornelis (8/24/2010)
Here is an interesting experiment (works on every client):
SELECT ROUND(789.98,-3) AS WeirdCol
INTO WeirdTable;
go
EXEC sp_help 'WeirdTable';
go
UPDATE WeirdTable
SET WeirdCol = WeirdCol - 0.001;
go
DROP TABLE WeirdTable;
go

Hugo what is the expected result? On my system it's an empty table (nothing weird about the table though :cool:) and the update operates on 0 rows - therefore nothing is really happening further. Are you referring to the fact that the table gets created despite the arithmetic overflow?

Regards,

Hrvoje Piasevoli

ziangij SSCrazy Eights Points: 8066 More actions · Answer 11

Hugo Kornelis (8/24/2010)
ziangij (8/23/2010)
plz. refer http://technet.microsoft.com/en-us/library/ms175003.aspx
for
select ROUND(748.58, -3)
result displayed is 1000.00
but when i execute the same i get an error:
An error occurred while executing batch. Error message is: Arithmetic Overflow.
any suggestions plz. ?
I get the same error. The explanation is simple, and already given by tommyh: "748.58" is considered to be numeric(5,2), and the result of ROUND is the same data type as its input. But 1000.00 does not fit in a numeric(5,2).
The fix is simple - use an explicit CAST to get a data type that does have some room for expansion:
SELECT ROUND(CAST(748.58 AS decimal(6,2)), -3);
tommyh (8/24/2010)
With every setting at the default however SQL allows this. Now i havent been able to replicate the error.
Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?

Microsoft SQL Server 2005 - 9.00.4294.00 (X64) Enterprise Edition (64-bit)

ziangij SSCrazy Eights Points: 8066 More actions · Answer 12

hrvoje.piasevoli (8/24/2010)
Hugo Kornelis (8/24/2010)
Here is an interesting experiment (works on every client):
SELECT ROUND(789.98,-3) AS WeirdCol
INTO WeirdTable;
go
EXEC sp_help 'WeirdTable';
go
UPDATE WeirdTable
SET WeirdCol = WeirdCol - 0.001;
go
DROP TABLE WeirdTable;
go
Hugo what is the expected result? On my system it's an empty table (nothing weird about the table though :cool:) and the update operates on 0 rows - therefore nothing is really happening further. Are you referring to the fact that the table gets created despite the arithmetic overflow?
Regards,
Hrvoje Piasevoli

yeah that's really strange... table is created..

select queries gives an arithmetic overflow error...

sjimmo SSChampion Points: 11139 More actions · Answer 13

Great QOD Hugo. Really makes you think.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Dennissinned Ten Centuries Points: 1318 More actions · Answer 14

Dennissinned

Ten Centuries

Points: 1318

August 24, 2010 at 6:42 am

#1210963

Thanks Hugo. Great Question.