Solved

Arithmetic overflow error converting numeric to data type numeric.

Posted on 2009-07-15
3
510 Views
Last Modified: 2012-05-07
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.
0
Comment
Question by:anand_dan27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24865923
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.
0
 

Author Comment

by:anand_dan27
ID: 24866014
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
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 24868517
select convert ( numeric(38,16), 200027.00000000000000000000  
*   8003240437419683.00000000000000000000)

That statement is indicating that, of the 38 digits in the result, 16of them are to the right of the decimal place which leaves 22 of them are to the left.
select convert ( numeric(38,20), 200027.00000000000000000000  
*   8003240437419683.00000000000000000000)

While the statement just above is indicating that of the 38 digits in the result, 20 of them are to the right of the decimal place which leaves only 18of them to the left of 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 left of 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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question