Team,
i have a requriement like this : for the given date in the past , I need to add months to the date until we are above the current date.
code snippet goes like this:
here v_nextpaydate is the given past date for ex : '29-jan -2016' and p_businessdt is the current date .for ex : 07/09/2016
WHILE (v_nextpaydate < TO_DATE (p_businessdt, 'MM/DD/YYYY'))
LOOP
v_nextpaydate := ADD_MONTHS (v_nextpaydate, 1);
END LOOP;
i have done it and it is fine , but am struck in one position. if we take the above example and on adding the months consecutively, we need to get 29th of the consecutive months, but in the second iteration we are getting 29-feb-2016,and is correct , and here comes the problem from third iteration , where in it has to be 29-mar-2016 ,but it turns to be 31-mar-2016 since 29-feb-2016 is last day of the month in feb and hence we are getting 31-mar-2016 in third iteration , instead of 29-mar-2016. and on further iterations it goes to the last day of the consecutive months like (30-apr-2016,31-may-2016,30-jun-2016 and 31-july-2016).
can any one assist me in writing the code which on adding a month to the given date , we need to get the same date of previous month,but the month has to be current month as explained above.
Moreover if the given past day is the end of the month then we need to go with the end of the month concept and it can be handled with the above code snippet itself.
the problem is for dates of 28th,29 feb and 30 of few months.
Please suggest