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.

select convert ( numeric(38,20), 200027.0000000000000000000

* 8003240437419683.000000000

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.0000000000000000000

* 8003240437419683.000000000

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.0000000000000000000

* 8003240437419683.000000000

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.

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.

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

select convert ( numeric(38,16), 200027.0000000000000000000

* 8003240437419683.000000000

and this statement is throwing an error

select convert ( numeric(38,20), 200027.0000000000000000000

* 8003240437419683.000000000

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

select convert ( numeric(38,16), 200027.00000000000000000000 00000000000) * 8003240437419683.000000000

That statement is indicating that, of the

38digits in the result,16of them are to the right of the decimal place which leaves22of them are to the left.select convert ( numeric(38,20), 200027.00000000000000000000 00000000000)

* 8003240437419683.000000000

While the statement just above is indicating that of the

38digits in the result,20of them are to the right of the decimal place which leaves only18of them to theleftof the decimal. Actually, I think I may have misscounted there may only be 22 digits to the left of the decimal in the results of that calculation.Yup, I did the calculation in teh Calculator on my machine and then cut & pasted the result into Word where I checked the letter count and it came out to 22.

The net result of that is that, if the column you are putting the data in is Number(38,20) you cannot put that value into that column. Your choices are either to expand the column to something like Number (45, 20) or to forfeit some of the decimal places and change it to something like Number(38,12). Notice, I am providing a little cushion for the values to the

leftof the decimal place.Your decision should be guided by your knowledge of the data but, if the example you provided is more typical of your values (i.e. your values have little other than zeros to the right of the decimal), I'd be tempted to go with the second one.