Oracle addmonths poses issue for certain dates.

Oracle 12c - add months to a date poses interesting case:
Issue:  I need to move rows containing column renewal with certain date  to future date
            Later when I am done my work, I want to return those rows back to what they  were before.
            But in doing that I don't get original date for some records.

Example:
I have  some rows with renewal date of Feb 28, 2016, and Feb 29,2016.
I move these rows by changing their renewal to future date like this:

     select add_months( TO_DATE('28-Feb-2016','dd-Mon-yyyy'),  12*5 ) dt from dual;
     select add_months( TO_DATE('29-Feb-2016','dd-Mon-yyyy'),  12*5 ) dt from dual;

when I have return the date back  from '28-Feb-2021',   I don't get 28Feb2016 anymore:

         select add_months( TO_DATE('28-Feb-2021','dd-Mon-yyyy'), -12*5 ) dt from dual;
 gives Feb 29, 2016.

How to correct this? :(
ALad2005Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aranaCommented:
This is expected behavior (not always desirable tho), according to oracle docs " 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. " 
 
you would have to work something like

LEAST(
        ADD_MONTHS(TO_DATE('28-Feb-2016','dd-Mon-yyyy'),-12*5),
        ADD_MONTHS(TO_DATE('28-Feb-2016','dd-Mon-yyyy')-1,-12*5)+1
    )
 

Open in new window

0
Bill PrewIT / Software Engineering ConsultantCommented:
The bottom line is you can't do what you want to do, but for good reason.  29FEB is a problem date in that it only occurs every 4 years.  So when you ask Oracle to add 5 years to that day, it has no choice but to give you 28FEB five years in the future, since there is no 29FEB that year.  But of course either 28FEB of the current year will also map to that date.  So you have a situation where two different source dates map to a single date 5 years out.  At that point there is nothing different about the two 28FEB dates that you changed both 28FEB2016 and 29FEB2016 from, so there is no way to "reverse" the process for 28FEB2021 and get the actual original date.

You could add 5*365 days to your current dates, like:

     select TO_DATE('28-Feb-2016','dd-Mon-yyyy') + 365*5 dt from dual;
     select TO_DATE('29-Feb-2016','dd-Mon-yyyy') + 365*5 dt from dual;

This has the advantage of being reversible, subtracting the same amount will always get you back to where you came from:

     select (TO_DATE('28-Feb-2016','dd-Mon-yyyy') + 365*5) - 365*5 dt from dual;
     select (TO_DATE('29-Feb-2016','dd-Mon-yyyy') + 365*5) - 365*5 dt from dual;

But the downside of that is that since a years is not exactly 365 days, then 2/1/2016 will map to 31JAN2021, which may not be desirable.  Depends on the business logic.

Given all that, if you need to stay with adding months, then you will probably have to save off the current dates and recover them from that saved data, rather than "reverse" the +5 years calculation.  You really only need to do that for the 29FEB exceptions that exist in the data though, so you could just save those off and update them after.


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ALad2005Author Commented:
I see.  Thank you Bill and Arana for your input.   You are right, there is no magic.  
I like Bill's solution.  I think it's best for my situation.
To move date record to future I need to use  :
            select date  plus a number from dual

 and then when need to revert apply   :
             select date minus same number from dual  

Thank you,.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome, glad that was helpful.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.