Date arithmetic errors if return values are outside of valid SQL ranges

[rebkwok]

SQLServer has a range of valid dates; for Date this is 0001-01-01 - 9999-12-31 and for Datetime it is 1753-01-01 - 9999-12-31.

If we add or subtract a day/month/year and this results in a value which is outside of the valid range, we get a somewhat cyrptic error message:
Adding a value to a 'datetime' column caused an overflow

This is most likely to occur when adding to a date, since TPP uses the ceiling date 9999-12-31 to signify null values. Hence gp_contact_date_2 in the following study definition extract throws the error if there are any patients with consultation entries which have no (i.e. ceiling) SeenDate recorded:

  gp_contact_date_1 = patients.with_gp_consultations(
          returning="date",
          on_or_after="index_date",
          date_format="YYYY-MM-DD",
          find_first_match_in_period=True
      ),

  gp_contact_date_2 = patients.with_gp_consultations(
        returning="date",
        on_or_after="gp_contact_date_1 + 1 day",
        date_format="YYYY-MM-DD",
        find_first_match_in_period=True
    ),

A workaround in this case is to use between rather than on_of_after, to set an upper limit on the returned dates, which is lower than the ceiling. e.g. between=["index_date", "2100-12-31"] in the initial gp_contact_date_1 variable should capture all valid consultation dates while omitting the ceiling dates.

However, it would also be good to add a check to date arithmetic and either deal with ceiling values (return the value unchanged if we try to do a DATEADD with a ceiling value?) or raise a more informative error to the user.