Arithmetic overflow error converting numeric to data type numeric.

Question

3.67/5 (3 votes)

See more:

SQL-Server-2008

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:

SQL

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

Saral S Stalin

Updated 28-Mar-23 7:35am

Add a Solution

Comments

VJ Reddy 30-May-12 7:43am

Thank you, Saral S Stalin, for accepting the solution :)

3 solutions

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

Posted 16-Apr-12 4:21am

Mehdi Gholam

Comments

Saral S Stalin 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 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 25-Apr-13 16:30pm

Very Good!

VJ Reddy 18-Apr-12 6:10am

Good answer. 5!

Mehdi Gholam 18-Apr-12 6:33am

Thanks VJ!

Solution 5

Hi,

NUMERIC(18, 10) doesent have 18+10 digits, that anotation means that you have 18 digits and 10 of those are on the decimal places.

If you want 18 unit digits and 10 decimal digits you declare this way: NUMERIC(28,10)

Posted 28-Mar-23 7:35am

Member 15964288

Add a Solution

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

VJ Reddy · Accepted Answer · 2012-04-18T00:09:00

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

SQL

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.

Arithmetic overflow error converting numeric to data type numeric.

3 solutions

Solution 1

Solution 2

Solution 5

Add your solution here

Preview 0

Arithmetic overflow error converting numeric to data type numeric.

3 solutions

Solution 1

Solution 2

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us