Arithmetic overflow error converting numeric to data type numeric

Question

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.

it has values like 'N/A','100.0' or '99.1' ... i.e) it has both float and nvarchar — goofyui

Mitch Wheat Mitch Wheat · Accepted Answer · 2011-08-29T14:49:20

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

Arithmetic overflow error converting numeric to data type numeric

2 Answers