sql-servertype-conversionsqlcode

Arithematic Overflow : "Converting numeric to data type numeric" error

There are two columns having datatype Decimal (16,4)

  1. OwnedShares
  2. TotalOutstandingShares

I am trying to compute percentage using these two columns and then converting it to Decimal(7,4).

Code Being used:

Convert(Decimal(7,4),Case when OwnedShares = 0 or TotalOutstandingShares = 0 then 0 else ((OwnedShares/TotalOutstandingShares)*100) end)

I am getting Error message : 'Arithmetic overflow error converting numeric to data type numeric.'

Can someone help to figure me out where am I going wrong?

Solution

  • If you're getting an arithmetic overflow error, then it's likely that your percentage calculation is returning at least one record which is greater than 100.0000 (or 100%). Try increasing the decimal's precision to increase the number of digits that can be stored until the error no longer occurs, e.g. DECIMAL(8,4) or greater.