Adding date intervals can’t handle going past the end-CodePudding

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.

CodePudding user response：

This has been answered here:

Oracle DB Ora-01839: date not valid for month specified. 29-02-2016 leap year

Basically, that is just the way Oracle implemented it