Error: “Arithmetic overflow error converting numeric to data type numeric.”

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s