Arithmetic overflow error converting expression to data type, when generating age from two variables

Question

I am trying to extract data from two tables, first generating years registered for each person on a database, and then joining to another table to limit the age group, with the aim of getting a sum of total years registered for all people in the criteria. I currently get the error message

'arithmetic overflow error converting expression to data type’.

The obsdate variable is a 'YYYY-DD-MM' (date format), while yob is 'YYYY' (smallint) , but I thought that using YEAR(obsdate) had resolved this, and the issue may be elsewhere?

I saw about adding 'CAST(subquery.yearsreg AS BIGINT)' which gave me a far higher number than expected, and I'm not sure it achieved what I was hoping for?

SELECT SUM(subquery.yearsreg)
FROM (
    SELECT id, yob,
        (CASE
        WHEN pat.deathdate IS NOT NULL AND pat.deathdate <= pat.regenddate THEN (YEAR(pat.deathdate)-YEAR(pat.regstartdate))
        WHEN pat.deathdate IS NOT NULL AND pat.deathdate > pat.regenddate THEN (YEAR(pat.regenddate)-YEAR(pat.regstartdate))
        WHEN pat.deathdate IS NULL AND pat.regenddate IS NOT NULL THEN (YEAR(pat.regenddate)-YEAR(pat.regstartdate))
        ELSE YEAR(getdate())-YEAR(pat.regstartdate)
        END) AS yearsreg
    FROM Patient AS pat
) AS subquery
INNER JOIN Observation AS obs ON subquery.id = obs.id
WHERE obs.obsdate > '2004-12-31' AND obs.obsdate <= '2018-12-31'
    AND ((YEAR(obs.obsdate))-subquery.yob) > 15 AND ((YEAR(obs.obsdate))-subquery.yob) < 45

What are the table schemas? Can you provide us with some sample data and expected results? — digital.aaron
Welcome to Stack Overflow, Katie. To help us help you, please take the Tour and read through How To Ask. Take a look at this well-structured question, then edit your question with the details needed to create a Minimal, Complete, and Verifiable Example for database-related questions. — Eric Brandt
Have you checked whether the yob field has any null values, and eventually excluded them from the query? — devtech

devtech devtech · Accepted Answer · 2020-02-20T21:09:49

Try this:

SELECT SUM(subquery.yearsreg)
FROM (
  SELECT id, CONVERT(INT,yob),
    (CASE
      WHEN pat.deathdate IS NOT NULL AND pat.deathdate <= pat.regenddate THEN (YEAR(pat.deathdate)-YEAR(pat.regstartdate))
      WHEN pat.deathdate IS NOT NULL AND pat.deathdate > pat.regenddate THEN (YEAR(pat.regenddate)-YEAR(pat.regstartdate))
      WHEN pat.deathdate IS NULL AND pat.regenddate IS NOT NULL THEN (YEAR(pat.regenddate)-YEAR(pat.regstartdate))
      ELSE YEAR(getdate())-YEAR(pat.regstartdate)
    END) AS yearsreg
  FROM Patient AS pat
  WHERE pat.yob IS NOT NULL AND pat.regstartdate IS NOT NULL
) AS subquery
INNER JOIN Observation AS obs ON subquery.id = obs.id
WHERE obs.obsdate > '2004-12-31' AND obs.obsdate <= '2018-12-31'
  AND ((YEAR(obs.obsdate))-subquery.yob) > 15 AND ((YEAR(obs.obsdate))-subquery.yob) < 45

Try and convert yob to int, just for the sake of it, just in case of some type clash situation. YEAR() converts to int, and it should be the one who determines the size of the result, but you never know.. Also regstartdate could be null, and i think YEAR() will convert it in 1900. I don't have an SQLServer to try on right now. Try also to run a couple of queries to find out if some user inserted crazy dates like 12-31-9999, or 01-01-1753 (apparently datetime goes that far).

Arithmetic overflow error converting expression to data type, when generating age from two variables

1 Answers