CODEWITHSUNDEEP
sqlmathoverflowdecimal
Mekha
Mekha

Reputation: 73

Encountered arithmetic overflow error using decimal datatype in SQL server 2008

I have a column declared as decimal(4,4). Currently it stores 0.0400 as the value. Now I need to update the value stored from 0.0400 to 9.95.I used the following query :

Update <tablename>
set <columnname>= 9.95
where <condition>

When I try to execute, I get the following error : Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.

Kindly help.

Upvotes: 3

Views: 6327

Answers (4)

Donnna
Donnna

Reputation: 11

Decimal(6,2) means:

At most 4 digits to the left of the decimal point, since you can have 2 to the right of the decimal point. (even if you don't see them, or use them)

It is VERY wrong to say "you can have 6 digits total". 12345 is less than 6 digits total. But it'll overflow.

I'm not sure why SQL didn't just make it "Decimal(left, right)" and you would instantly see the limits you can store there.

Upvotes: 1

BMG
BMG

Reputation: 489

I had similar issue few years ago, here is my understanding.

Decimal(TotalDigitsIncludingDecimal,DecimalPlaces)

Eg: Value = 50.05 declare Decimal(4,2) Eg: Value = 5000.0005 declare Decimal(8,4)

Upvotes: 1

Andomar
Andomar

Reputation: 238096

If you write decimal(4,4), what the database hears is:

  • There are four digits total
  • All four of them are behind the decimal separator

So a decimal(4,4) can store the range 0.0000 to 0.9999 (and its negative equivalent). But 9.95 is outside that range, so it will return an error.

Upvotes: 1

Widor
Widor

Reputation: 13275

Defining a column as decimal(4,4) is the equivalent of saying

I will have 4 digits in total, of which 4 come after the decimal point

To represent 9.95, you'd need to store it as 9.9500 to satisfy the '4 decimal places' condition. But this now exceeds the 'max 4 digits' condition and so can't be converted.

You'd need at least decimal(5, 4) to store 9.95 as a decimal in this way.

Upvotes: 5

Related Questions