using ROUND Function with passing numeric expression to 9.5 and length 0 - by JygzVentura

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 364387 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 8/28/2008 4:11:25 PM
Access Restriction Public



I am trying to use builtin ROUND function in SQLServer2005 in our project.
I tried the function first on how it works, I use 9.5 as the numeric expression(1st parameter of the function) and 0 as the length(2nd parameter). I get an error message saying "An error occurred while executing batch. Error message is: Arithmetic Overflow."..
I tried 9 (as 1st parameter) with decimal places less than 5 (e.g. 9.4, 9.3...) it works well..
I tried again 9 (as 1st parameter) with higher decimal places (eg. 9.6,9.7...) I got same error message.
I tried again with other numbers like 99.5, 99.6, 999.5, 9999.5....and so on, same error message went up... But when I tried other numbers it worked well..

Examples of working statements:

Examples of non-working statements:

That's all..

Thank you, and More power!!!
Sign in to post a comment.
Posted by Jim [MSFT] on 9/2/2008 at 9:29 AM

Thankyou for this report. The behavior you see is "by-design", but it is surprising. Here's what's going on.

When you type: ROUND(9.5, 0) the compiler parses that literal of 9.5 and converts it into a datum whose type is numeric(2,1). The ROUND function then works out the result as 10.0 and tries to store it into the numeric(2,1) datum - alas, not enough room, we it reports "arithmetic overflow". It's as if you had written this snippet:

DECLARE @x numeric(2,1); SET @x = 9.5;

If you make more room in the datum, everything works fine. For example:

DECLARE @x numeric(3,1); SET @x = 9.5;

How can you find out the type that your literal has been converted to? Here's a nifty trick a colleague showed me that does what you need:

select SQL_VARIANT_PROPERTY (cast(9.5 as SQL_VARIANT), 'BaseType'),
         SQL_VARIANT_PROPERTY (cast(9.5 as SQL_VARIANT), 'Precision'),
         SQL_VARIANT_PROPERTY (cast(9.5 as SQL_VARIANT), 'Scale') ;

You can use this, of course, for other literals to help figure out what's going on 'under the hood'.

Hope this helps,

Jim (Hogg)
Posted by DB007 on 8/29/2008 at 5:20 AM
Alternativly, try the following:

select round('09.55',0)

-- This produces the result of 10. As the ' ' ensures this formats the data on implicit conversion to decimal(4,2).
Posted by DB007 on 8/29/2008 at 5:15 AM
This is because the 9.5 is being treated as a numeric(2,1) - implicitly, it cannot put in 10.0 into this data-type, hence the overflow error.

For example the following will not work:
declare @myvar numeric(6,2)
set @myvar = 9999.91
select @myvar
select ROUND(@myvar ,0);
-- Generates error.

The following will work:
declare @myvar numeric(7,2) /*As this can fit 10,000 into the value*/
set @myvar = 9999.91
select @myvar
select ROUND(@myvar ,0);

So in your example, you would have to do something like:
select round(cast(9.55 as numeric(4,2)),0) -- Works.

-- The following does not work:
select round(cast(9.55 as numeric(3,2)),0) /*This is whats hapening under the hood - overflow, as 955 = numeric(3,2) by default*/