An arithmetic overflow error occurred while converting varchar to numeric data type in SQL Server 2008

Question

I have a table in SQL Server 2008:

Create table ExTable1 
(
     Fcur varchar(50),
     Tcur varchar(50),
     Rt numeric(10,10)
)

I am trying to insert the data

Insert into [DB].[dbo].[ExTable1 ](Fcur, Tcur, Rt) 
values ('INR', 'EUR', 1)

Error I am getting is

An arithmetic overflow error occurred while converting varchar to numeric data type.

When I change datatype of column Rt to numeric(10,2), above mention query mentioned perfectly fine.

Data which I am going to insert in Rt column may have 10 digits after decimal.

How to fix this?

Basically I got this exception in C# code. So I tried executing the QueryString which was create in C# , directly in SQL server. I faced the same error.

Thanks

NUMERIC(10,10) will only hold values < 1

Alex K.
– Alex K.

2016-02-03 15:20:51 +00:00
Commented Feb 3, 2016 at 15:20 — Alex K.
– Alex K., Commented Feb 3, 2016 at 15:20

Gottfried Lesigang · Accepted Answer · 2016-02-03 15:22:11Z

3

Numeric(10,10) will define a number consisting of decimals only...

Try it with Numeric(11,10) (one place before and 10 after the comma).

The first parameter defines the full width, the second the number of places behind the comma within the full width, read here.

answered Feb 3, 2016 at 15:22

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ali Elmi · Accepted Answer · 2016-02-15 21:19:26Z

decimal[ (p[ , s] )] and numeric[ (p[ , s] )] Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale) The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point. The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Collectives™ on Stack Overflow

An arithmetic overflow error occurred while converting varchar to numeric data type in SQL Server 2008

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related