Encountered arithmetic overflow error using decimal datatype in SQL server 2008
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.
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.
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)
Read more here: Source link