• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 34
  • Last Modified:

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? :(
0
ALad2005
Asked:
ALad2005
  • 2
2 Solutions
 
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 PrewCommented:
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
 
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 PrewCommented:
Welcome, glad that was helpful.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now