11,935,066 members (51,895 online)
Rate this:
See more:
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 4:56am
VJ Reddy 30-May-12 7:43am

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

Rate this:

## Solution 1

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

Mehdi Gholam 18-Apr-12 6:33am

Thanks VJ!
Rate this:

## 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.
Mehdi Gholam 18-Apr-12 6:34am

5'ed
VJ Reddy 18-Apr-12 7:07am

Thank you, Mehdi.

-- Create Table --
create table M_DCR_Refund
(
refund_id int IDENTITY(1,1) primary key,
dcr_id int not null,

royalty numeric(18,2) not null,
rp numeric(2,2) not null,
stid int not null,
st numeric(2,2) not null,
tdsid int not null,
tds numeric(2,2) not null,
froyalty numeric(2,2) not null,
refund numeric(2,2) not null,

refund_date date not null,
refunded_by varchar(20) not null,
del_flag bit null
)
-- Insert Statement --
insert into M_DCR_Refund (
dcr_id,royalty,rp,stid,st,tdsid,tds,froyalty,refund,refund_date,refunded_by) values

-- Error Message--
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

Top Experts
Last 24hrsThis month
 George Jonsson 440 Sergey Alexandrovich Kryukov 342 Kornfeld Eliyahu Peter 230 Dave Kreskowiak 229 Richard Deeming 225
 George Jonsson 400 Dave Kreskowiak 235 Kornfeld Eliyahu Peter 230 Sergey Alexandrovich Kryukov 222 Richard Deeming 185