Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Round up or down IV Expand / Collapse
Author
Message
Posted Monday, August 23, 2010 9:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 5,967, Visits: 8,221
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
Post #973826
Posted Monday, August 23, 2010 9:38 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, 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 :)

Regards,

Hrvoje Piasevoli


Hrvoje Piasevoli
Post #973829
Posted Monday, August 23, 2010 10:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
Again good question on same round function.Thanks hugo

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #973837
Posted Monday, August 23, 2010 10:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,003, Visits: 369
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. ?
Post #973847
Posted Tuesday, August 24, 2010 12:02 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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.
Post #973864
Posted Tuesday, August 24, 2010 12:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,570, Visits: 10,447
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #973868
Posted Tuesday, August 24, 2010 12:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 5,967, Visits: 8,221
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
Post #973876
Posted Tuesday, August 24, 2010 12:44 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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.
Post #973879
Posted Tuesday, August 24, 2010 1:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 5,967, Visits: 8,221
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
Post #973887
Posted Tuesday, August 24, 2010 2:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 1,629, Visits: 5,573
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!
Post #973921
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse