Search code examples
sqlsql-servert-sqltype-conversion

Arithmetic overflow error converting numeric to data type numeric

I am facing an error on my SQL script:

Arithmetic overflow error converting numeric to data type numeric

select x.MemberName,x.DOB,x.FilePath,x.Medication,x.NDC,x.Directions,x.Name,x.Strength,x.GenericName,x.QtyOrdered,x.DaysSupply,x.DateFilled, 
CASE
    WHEN x.test = 0  THEN 'N/A'
    WHEN compliance > 100.0   THEN '100.0'
    ELSE CONVERT(VARCHAR(5), CAST(FLOOR(compliance *10)/10.0 AS DECIMAL(3,1)))
END as [Compliance]

I am facing the error on just above syntax line.

Solution

  • Here's your problem:

    declare @compliance decimal(10,5)

set @compliance = 100.0  --  <----------------

select CAST(FLOOR(@compliance *10)/10.0 AS DECIMAL(3,1))

    Throws "Arithmetic overflow error converting numeric to data type numeric" error. Changing to DECIMAL(4,1) works, or as @paola suggests, change your condition to >= 100.0

    decimal(p,s):

    p (precision) is 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) is the number of decimal digits that will be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point.

    In your case decimal(3, 1) means a total of 3 digits with 1 digit to the right of the decimal point, 

    99.9

    whereas decimal(4,1) provides a total of 4 digits with 1 digit to the right of the decimal point,

    999.9