asked on

Arithmetic overflow error converting numeric to data type numeric.

I found the error occurred in the below sample selection queries

select convert ( numeric(38,20), 200027.00000000000000000000
* 8003240437419683.00000000000000000000)

The above query will give the following error :
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting numeric to data type numeric.

However, I tried to modify the above the query and limited the scale point as per below and can solve the above error .

select convert ( numeric(38,16), 200027.00000000000000000000
* 8003240437419683.00000000000000000000)

But, this will create new problem when apply the value into an update statement as per below sample query.

DECLARE @OBJ numeric(38,20)

SELECT @OBJ = convert ( numeric(38,16), 200027.00000000000000000000
* 8003240437419683.00000000000000000000)

UPDATE tmpcalcumval
SET CumAdjFac = convert ( numeric(38,20),@OBJ)
WHERE TABID = 1

Msg 8115, Level 16, State 6, Line 3
Arithmetic overflow error converting numeric to data type numeric.

(1 row(s) affected)

Currently, seeking for your solution for the above matter. Appreciated, your kind attention.

8080_Diver

The initial problem occurs because the computed value has 20 decimal places and 23 characters to the left of the decimal place. Thus it violates the specification of (38,20) [i.e. a total of 38 characters with the right 20 being decimal places].
The second one works fine until you try to put it into a Numeric(38,20) slot and then, once again, it has 23 characters to the left of the decimal and 20 to the right and that violates the Numeric(38,20) that you are implicitly converting it to.

anand_dan27

ASKER

Thanks for the reply.

I am confused now. Why this statement is not throwing an error.

select convert ( numeric(38,16), 200027.00000000000000000000
* 8003240437419683.00000000000000000000)

and this statement is throwing an error

select convert ( numeric(38,20), 200027.00000000000000000000
* 8003240437419683.00000000000000000000)

Is there any solution for me to update the value in the table

ASKER CERTIFIED SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial