Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


4
1
Sign in
to vote
Type: Bug
ID: 364387
Opened: 8/28/2008 4:11:25 PM
Access Restriction: Public
1
Workaround(s)
view
1
User(s) can reproduce this bug

Description

Hi,

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:
ROUND(9.4)
ROUND(3.5)
ROUND(10.4)

Examples of non-working statements:
ROUND(9.5,0)
ROUND(9.6,0)
ROUND(99.5,0)
ROUND(999.5,0)
ROUND(9999.5,0)
ROUND(-9.5)

That's all..

Thank you, and More power!!!
Details
Sign in to post a comment.
Posted by Microsoft on 9/2/2008 at 9:29 AM
Hi,

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;
SELECT ROUND(@x, 0);

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

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

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*/
Sign in to post a workaround.
Posted by DB007 on 8/29/2008 at 5:23 AM
select round('09.55',0) -- this works.
select round('0999.5',0) -- this works.

The '' and the adding of the 0 ensures it will implicitly convert to a decimal data-type with enough length to store the rounded number.