Arithmetic overflow error for DATEDIFF on SQL Server

[ob325]

Line 284 in AggregateCovariates.R ...

sql <- "select
  cohort_definition_id,
  count(*) row_count,
  count(distinct subject_id) person_count,
  min(datediff(day, cohort_start_date, cohort_end_date)) min_exposure_time,
  avg(datediff(day, cohort_start_date, cohort_end_date)) mean_exposure_time,
  max(datediff(day, cohort_start_date, cohort_end_date)) max_exposure_time
  from
  (select * from #agg_cohorts_before union select * from #agg_cohorts_extras) temp
  group by cohort_definition_id;"

Casting to bigint seems to fix (vinci-ohdsi/characterization@develop)

[jreps]

Thanks for the fix. I was just thinking though, it may be worth inspecting the cohort dates as I think this means for some patient the date diff in days was > 2147483647? Something may be off as that is a lot of years. Edit, actually - it may just be the temp sum for the average that is > 2147483647 which could easily happen if the cohort is large, so if the cohort is large, then the dates are probably fine, if the cohort is small, might be worth checking the dates.