I’m not very familiar with Oracle, so all of this is a bit new to me.
If I have a simple calculation such as below:
WITH dates AS (SELECT date '2020-01-31' AS jan31, date '2020-02-29' AS feb29 FROM dual)
SELECT
jan31 + interval '1' month AS nextmonth, -- 29 Feb 2020
feb29 + interval '1' year AS nextyear -- 28 Feb 2021
FROM dates;
both calculations would fail because the result is out of range.
I have tried the same in PostgreSQL, MariaDB and Microsoft SQL Server, using their own corresponding expressions, and they all result in the commented dates; that is, they clip the result to the end of the month rather than overflow.
I am familiar with the add_months function, which does make the appropriate adjustment, but I wonder whether there is a way of getting the expression date + interval to work reliably.