Today I learned that if you get this error, it is probably because you did something weird with your precision and scale parameters while defining a numeric datatype.
EG – the following portion of a select statement was used to determine patient age at service:
CAST((DATEDIFF(d, PT.DOB, CASE.DOS)/365.25) AS numeric(3,1)) AS PtAge
I received the following error and the query was terminated:
“Arithmetic overflow error converting numeric to data type numeric.”
This happened because I was imagining all patients were under 100 years old in the population I was querying.
The “numeric(3,1)” portion of the clause means that the number of numeric characters allowed (precision) of 3 conflicted with the number of characters after the decimal point (scale) of 1 for patients who were over 99 years old.
The corrected portion of this query is to increase the precision to 4, so that patients with ages over 100 would not be excluded.
CAST((DATEDIFF(d, PT.DOB, CASE.DOS)/365.25) AS numeric(4,1)) AS PtAge
This allowed for ages like “101.2” because there were 4 total characters, one of which was after the decimal.