Interval arithmetic to correctly deal with end of month - Oracle SQL
- by user2003974
I need a function which will do interval arithmetic, dealing "correctly" with the different number of days in a month. For my version of "correctly" - see below!
First try
select to_date('31-May-2014') + interval '1' months from dual
This returns an error, because there is no 31st June. I understand that this behaviour is expected due to the ANSI standard.
Second try
select add_months(to_date('31-May-2014'),1) from dual
This correctly (in my use case) returns 30th June 2014, which is great. BUT
select add_months(to_date('28-Feb-2014'),1) from dual
returns 31st March 2014, when I want 28th March 2014.
Background
This has to do with legal deadlines. The deadlines are expressed in law as a number of months (say, 3) from a base date. If the base date is last day of the month and three months later the month is longer, then the deadline does NOT extend to the end of the longer month (as per the add_months function). However, if the base date is last day of the month and three months later the month is shorter, then the deadline expires on the last day of the shorter month.
Question
Is there a function that does what I need?
I have intervals (year to month) stored in a table, so preferably the function would look like:
add_interval_correctly(basedate DATE, intervaltoadd INTERVAL YEAR TO MONTH)
