SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Round up or down IV


Round up or down IV

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18207 Visits: 12426
Comments posted to this topic are about the item Round up or down IV


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
hrvoje.piasevoli
hrvoje.piasevoli
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 Visits: 510
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 Smile

Regards,

Hrvoje Piasevoli

Hrvoje Piasevoli
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2691 Visits: 1189
Again good question on same round function.Thanks hugo

Malleswarareddy
I.T.Analyst
MCITP(70-451)
ziangij
ziangij
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3569 Visits: 377
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
tommyh
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2484 Visits: 2000
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
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59009 Visits: 13297
Again, great question and great explanation.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18207 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
tommyh
tommyh
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2484 Visits: 2000
Hugo Kornelis (8/24/2010)
ziangij (8/23/2010)

[quote]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.760 SP3 Standard Edition
9.00.1399.06 RTM Developer 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
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18207 Visits: 12426
tommyh (8/24/2010)
Hugo Kornelis (8/24/2010)
ziangij (8/23/2010)

[quote]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.760 SP3 Standard Edition
9.00.1399.06 RTM Developer 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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
paul.knibbs
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4064 Visits: 6240
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search