Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi experts,
 
As per my understanding NUMERIC(18, 10) column would take 18 decimal digits to the left of the decimal point and 10 to the right.
 
One of our procedure started throwing 'Arithmetic overflow error converting numeric to data type numeric' error for much lesser values. I checked the documentation in msdn http://msdn.microsoft.com/en-us/library/ms187746.aspx[^] which also seems to say the same thing. Is there something I miss here?
 
Example:
 
SELECT CAST(1234567891234567.34 AS NUMERIC(18,2))  --Works
SELECT CAST(1234567891234567.34 AS NUMERIC(18,10)) --fails with Arithmetic overflow error
 
We fixed the problem by changing the casting to NUMERIC(18, 2) but I wanted to understand the problem in a little more depth.
Posted 16-Apr-12 3:56am
Comments
VJ Reddy at 30-May-12 7:43am
   
Thank you, Saral S Stalin, for accepting the solution :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Read this :
http://msdn.microsoft.com/en-us/library/ms187746.aspx[^]
 
NUMERIC (p,s) : p means both left and right of the decimal
 
Generally you can't have large numbers and small numbers with in the same context (handling millions of $'s and keeping track of 1/10000 of cents etc. as it would not make any difference at those scales).
  Permalink  
Comments
Saral S Stalin at 16-Apr-12 10:55am
   
Hi, I understand your points. But what I am looking for is why it throws an arithmetic overflow error when it should not.
Mehdi Gholam at 16-Apr-12 14:21pm
   
10 decimal places will be allocated ( check by querying the table) so you are left with only 8 places to the left.
 
The error is by design to show you are doing something wrong.
cdaraujo at 25-Apr-13 16:30pm
   
Very Good!
VJ Reddy at 18-Apr-12 6:10am
   
Good answer. 5!
Mehdi Gholam at 18-Apr-12 6:33am
   
Thanks VJ!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

The Solution 1 given by Mehdi Gholam is very good.
 
I want to add the following since the OP wants to know specifically why error was not thrown in the first case and why error is thrown in the second case.
 
As seen from the statements
SELECT CAST(1234567891234567.34 AS NUMERIC(18,2))  --Works
SELECT CAST(1234567891234567.34 AS NUMERIC(18,10)) --fails with Arithmetic overflow error
it is clear that, in the first case from the statement NUMERIC(18,2) the total digits are 18 and 16 digits are available to the left of decimal, whereas 1234567891234567 are 16 digits. Hence, there is no error.
In the second case, from the statement NUMERIC(18,10), 8 digits are available to the left of decimal, but 1234567891234567 are 16 digits which is more than 8 digits. Hence, Arithmetic overflow error occurs.
  Permalink  
Comments
Mehdi Gholam at 18-Apr-12 6:34am
   
5'ed
VJ Reddy at 18-Apr-12 7:07am
   
Thank you, Mehdi.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 380
1 _Amy 255
2 Richard Deeming 250
3 Nirav Prabtani 223
4 CHill60 175
0 _Amy 110
1 Maciej Los 90
2 OriginalGriff 85
3 Mehdi Gholam 80
4 Sergey Alexandrovich Kryukov 75


Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 28 Jul 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100