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
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
decimal(4,4) can store the range
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.
Eg: Value = 50.05 declare Decimal(4,2)
Eg: Value = 5000.0005 declare Decimal(8,4)
Read more here: Source link