Next Monthly Anniversary Date
Arnab Panja, April 01, 2020 - 2:53 pm UTC
Thanks for the answer.
I have changed the script now to use months_between function. The objective of the script was to do the below
1. Given a date (some time in the back say 28/03/1999)
2. Find out the monthly anniversary date just immediately after the system date.
So after making the changes as you have suggested if I run this using input date (for Point 1) as 28/03/1999 and a system date as 01/04/2020 what I get is 28/03/2020 back which is understood from the script.
Then I need to apply just one more add_months to arrive at the date I need.
I think this method gives me a consistent and correct answer all the time to the objective as I have mentioned above.
Thanks for helping me out.
~ Arnab P
April 02, 2020 - 3:25 am UTC
Glad we could help
Missing days
A. Olsen, July 23, 2020 - 7:13 am UTC
The method you suggested as a solution for the problem is the correct one indeed.
But I would like to ask if the design of add_months is good enough.
As you mention, the doc says: "If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date."
So it works as designed. The problem with this function is, that it's missing a few days each year.
Say for instance:
select add_months(to_date('30/11/2021','dd/mm/yyyy'),1) from dual;
ADD_MONTHS(TO_DATE('30/11/2021','DD/MM/YYYY'),1)
------------------------------------------------
31-DEC-21
If an application needs, for any reason, 29/11/2021 to return 29/12/2021, then the same application might expect 30/11/2021 to return 30/12/2021 - but 30/12/2021 is "hidden" by the function and cannot be achieved.
I would argue, that add_months should have a second, optional in parameter, that alters between "last day" in according to application specifications.
July 23, 2020 - 7:40 am UTC
If there are changes you'd like to see to Oracle Database features, please submit them to the ideas forum:
https://community.oracle.com/community/groundbreakers/database/database-ideas This enables us to see how much support there is for changes like this and prioritise appropriately.
Note you can get 30 Nov -> 30 Dec by adding an interval of one month. But this suffers the opposite problem, adding one month to 31 Oct -> 31 Nov, which is invalid!
select date'2020-11-30' + interval '1' month ,
add_months ( date'2020-11-30', 1 )
from dual;
DATE'2020-11-30'+INTERVAL'1'MONTH ADD_MONTHS(DATE'2020-11-30',1)
30-DEC-2020 00:00:00 31-DEC-2020 00:00:00
select date'2020-10-31' + interval '1' month
from dual;
ORA-01839: date not valid for month specified
About "Last Day" of Add_Months
Peter G, June 24, 2022 - 3:40 pm UTC
I agree with A. Olsen when saying
"add_months should have a second, optional in parameter, that alters between "last day" in according to application specifications."
Meanwhile, this is how I solved when running into this problem today, Reconciling reports made in pure SQL with same reports in Informatica,
turned out to have minor differences in some corner cases.
create or replace function infa_add_months(in_date date, in_months number)
return date deterministic
is
-- Implementation style Informatica's ADD_TO_DATE
-- Add_to_date adds one month like this:
-- Date 2022-03-31 + 1 Month = 2022-04-31
-- Since 2022-04-31 is not a valid date, then last day of the month
-- is returned, that is 2022-04-30. In the same way,
-- Date 2022-04-30 + 1 Month is 2022-05-30
l_date date;
begin
l_date := add_months(in_date, in_months);
-- Oracle's add_month will preserve the "Last Day", E.g:
-- Adding one month to 2022-04-30 results in 2022-05-31
-- Informatica does not do that, and will return 2022-05-30
-- We compensate by saying new day of month cannot be greater than
-- original day of month.
if extract(day from l_date) > extract(day from in_date)
then
l_date := l_date - (extract(day from l_date) - extract(day from in_date));
end if;
return l_date;
end infa_add_months;
/
June 27, 2022 - 12:52 pm UTC
Thanks for sharing
Leap year hickup
Martin Mierke, February 29, 2024 - 9:03 am UTC
At least it explains the logic behind it, but I was getting a result yesterday when comparing last year's data with actual data and that was showing based on this function, so instead of getting data for the 28th the query wanted to return data for the 29th.
SELECT ADD_MONTHS(TO_DATE('2023-02-27', 'yyyy-mm-dd'), 12), ADD_MONTHS(TO_DATE('2023-02-28', 'yyyy-mm-dd'), 12)
FROM DUAL;
ADD_MONTHS(TO_DATE('2023-02-27','YYYY-MM-DD'),12)
-------------------------------------------------
ADD_MONTHS(TO_DATE('2023-02-28','YYYY-MM-DD'),12)
-------------------------------------------------
27-FEB-24
29-FEB-24
Basically, while in 99.99% of the cases, add_months returns the wanted result, I would consider the behavior as a bug in ths situation.
March 01, 2024 - 1:58 am UTC
Our intent/assumption around ADD_MONTHS is that your primarily dealing with month related operations. For example, in a case of
where date_col < add_months(..., N)
then there would be a reasonable expectation that if you are on the end of a month, you want to get "N" *complete* months.
But I take your point. add_months,12 is a common technique to get next years same day.
A month is imprecise
Jonathan Taylor, March 01, 2024 - 4:53 pm UTC
I've seen conversations go back and forth on many occassions about this - I've wasted more time than I care to admit on this topic!
The fact is given a month isn't a precise unit of time - so there is always going to be vaguery and interpretation. You may as well try to define the length of a piece of string.
Given that that 28/2/2023 is the last day of Feburary 2023, then I would say 12 months later is the last day of Feburary 2024 - so 29/2/2024.
So The ADD_MONTHS(,12) function I would argue is the correct answer or at the very least an acceptable answer, not a bug.
Looking at it the other way, if we insist 28/2/2024 (not the 29th) is considered 1 year later than 28/2/2023, then presumably we can all agree one year earlier than 28/2/2024 is 28/2/2023.
So what is one year earlier than 29/2/2024? It can't be 29/2/2023 so what is is:-
* 1/3/2023 - This is in a completely different month, so could be perceived to be "wrong" (although I imagine many leap year birthdays are celebrated then)
* 28/2/2023 - so we are saying one year earlier than BOTH 28/2/2024 and 29/2/2024 falls on the same day, which could also be perceived to be "wrong".
We often get the same debate in any year, leap or not, when people don't like that one month after 31st January is 28th (or 29th) February - they see it as having less time to e.g. meet a deadline set by the application.
However, they can't give a precise definition of what constitutes one month, that works in all cases, both while adding and subtracting. Any suggestions they give are just as "perceviably wrong" and subject to a counter-argument as the problem they are trying to solve.
The same people who are paid monthly do not have a problem with technically being paid more per hour in February than any other month though!
If you are in the "28/2/2023 + 1 year = 28/2/2024" camp, you can always use NUMTODSINTERVAL:-
SELECT TIMESTAMP '2023-02-28 00:00:00' + NUMTOYMINTERVAL (1,'YEAR') oneyearlater
FROM DUAL;
ONEYEARLATER
---------------------------------------------------------------------------
28-FEB-24 00.00.00.000000000
However, this doesn't work so well in reverse....
SELECT TIMESTAMP '2024-02-29 00:00:00' - NUMTOYMINTERVAL (1,'YEAR') oneyearearlier
FROM DUAL;
*
ERROR at line 1:
ORA-01839: date not valid for month specified
March 05, 2024 - 1:31 pm UTC
Great points - whenever doing calculations in units of a month it's important to state exactly how you define these cases to work.