SQL Server - ROUND function

Asked By RichB on 20-Jul-07 06:19 AM
I need within a query to compare a couple of calculations, with their
results rounded. I have been trying to use the ROUND() function:
SELECT ROUND(-1.92, 0);

but find that I get the following error if the calculate value <1 and >0.

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

Is there another way to do this other than the obvious to add 1 to the
calculated value? Why is this occuring? Will round fail at any other level?

Many Thanks, Richard




Erland Sommarskog replied on 22-Jul-07 01:08 AM
RichB (rb6347@koanga.com) writes:

It sounds like you have a decimal column with a funky combination of
precision and scale. For instance:

declare @x decimal(9,9)
SELECT @x =    0.84
SELECT ROUND(@x, 0);

gives this error. Note that the error is not in SQL Server, but in
SqlClient. That is, I get this error in Mgmt Studio, but not in SQLCMD.
(Where I on the other hand get .00000000 which is just plain wrong.)

OSQL is the only to return a correct value.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Pawel Potasinski replied on 20-Jul-07 07:32 AM
This seems to be indeed SqlClient error. If you really want to see the
result in SSMS, try to cast the result to any type.

select cast(round(0.84,0) as decimal(2,1))

--
Regards
Pawel Potasinski


Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal w wiadomosci
Pawel Potasinski replied on 20-Jul-07 08:18 AM
The tricky thing is that you can even multiply by numeric to make it
working:

SELECT 1.0*ROUND(0.84,0)

--
Regards
Pawel Potasinski


Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal w wiadomosci
RichB replied on 20-Jul-07 08:27 AM
Thanks, that solves the problem.
Pawel Potasinski replied on 20-Jul-07 08:53 AM
There is workaround as it was shown but there is something interesting in
ROUND. Try this:

USE tempdb
GO

SELECT ROUND(0.95,0) AS col
INTO dbo.temp1
FROM sys.databases

SELECT col FROM dbo.temp1 -- error in SSMS

SELECT 1.0*col FROM dbo.temp1 -- ok

EXEC sp_help 'dbo.temp1' -- see Precision=2 and Scale=2 of col !!!

DROP TABLE dbo.temp1

It is possible to store 1.00 on decimal(2,2) :-)

--
Regards
Pawel Potasinski


U¿ytkownik "RichB" <rb6347@koanga.com> napisa³ w wiadomo¶ci
Erland Sommarskog replied on 22-Jul-07 01:09 AM
Pawel Potasinski (pawel.potasinski@gmail.com) writes:

And this works too:

declare @x TABLE(x decimal(4,2))
insert @x(x) VALUES(99.95)
update @x set x = round(x, 0)
select x from @x

You can cram 100 into decimal(4, 2) as well.

DB-Library and ODBC get this right. OLE DB produces 0 and SqlClient
freaks out. Hm...



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
RichB replied on 21-Jul-07 05:06 AM
Will this get raised with  MS as a defect, or do I need to record it
somewhere and if so where?
Erland Sommarskog replied on 22-Jul-07 01:10 AM
RichB (richb@community.nospam) writes:

Depends on what you consider to be a defect.

SQL Server MVP Steve Kass has a filed a bug on the unexpected result
of the rounding:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286267

Myself, I have filed a bug on OLE DB returning an incorrect result:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288555

If you think the error message in Mgmt Studio is inappropriate, you
should file a bug for the .Net Framework, not Mgmt Studio, as SSMS is
just passing the bucket. Personally, I lean towards the position that
the behaviour in SqlClient is acceptable, since the value is really
out of band. And I suspect that addressing it, could be a major task
that could have far-reaching effects that would be difficult to warrant.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
RichB replied on 23-Jul-07 10:13 AM
Many Thanks...
Erland Sommarskog replied on 23-Jul-07 06:27 PM
Erland Sommarskog (esquel@sommarskog.se) writes:

Some more investigation showed that OLE DB as such is innocent. SQLCMD
gets it wrong, and so does IDataConvert, an interface I use in a OLE DB
applicaiton of mine.


And it was not really that simple either. Well, SqlClient is surely the
one who raises the error I would guess, but a simple ToString works. So
maybe the bug should be filed for SSMS anyway, and then that team will
have to pass that bucket.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Gaurav Jaiswal replied on 21-Jul-08 03:12 AM
ROUND



Returns a numeric expression, rounded to the specified length or precision.

Syntax



ROUND ( numeric_expression , length [ , function ] )

Arguments



numeric_expression



Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.



length



Is the precision to which numeric_expression is to be rounded. length must be tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal places specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.



function



Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Return Types



Returns the same type as numeric_expression.

Remarks



ROUND always returns a value. If length is negative and larger than the number of digits before the decimal point, ROUND returns 0.

Example 	       Result

ROUND(748.58, -4) 	0



ROUND returns a rounded numeric_expression, regardless of data type, when length is a negative number.

Examples 	        Result

ROUND(748.58, -1) 	750.00

ROUND(748.58, -2) 	700.00

ROUND(748.58, -3) 	1000.00



Examples

A. Use ROUND and estimates



This example shows two expressions illustrating that with the ROUND function the last digit is always an estimate.



SELECT ROUND(123.9994, 3), ROUND(123.9995, 3)

GO



Here is the result set:



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

123.9990    124.0000



B. Use ROUND and rounding approximations



This example shows rounding and approximations.

Statement 	                   Result



SELECT ROUND(123.4545, 2)         123.4500



SELECT ROUND(123.45, -2)          100.00





C. Use ROUND to truncate



This example uses two SELECT statements to demonstrate the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.

Statement 	                      Result



SELECT ROUND(150.75, 0)               151.00



SELECT ROUND(150.75, 0, 1)            150.00











Regards

Gaurav
Luigi replied to Pawel Potasinski on 14-Mar-11 12:12 PM
SELECT 1.0*ROUND(0.84,0)



gives me:





Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type numeric.



I'm using SQL Server 2008 version 10.50.1600.



Luigi
Luigi replied to Pawel Potasinski on 14-Mar-11 12:13 PM
SELECT 1.0*ROUND(0.84,0)



gives me:



Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type numeric.



I'm using SQL Server 2008 version 10.50.1600.



Luigi