Adding 1 Month less 1 Day or 1 Year less 1 Day

customaware

I have a need to assign an End Date to a particular BO such that the End Date is 1 day prior to the anniversary on the BO.StartDate. Anniversary type could be Monthly or Annually.

Can anyone suggest a Rule that could provide that date?

For example

Start Date End Date Monthly End Date Annually
13/04/14 12/05/14 12/04/15
01/01/14 31/01/14 31/12/15
01/07/14 31/07/14 30/06/15
01/03/14 28/02/14 28/02/15 except needs 29th for leap year.

Any help would be appreciated. I have scoured the Date functions provided but cannot see the wood for the trees for a solution.

kklosson

Use the DATE_ADD function. It seems that should work for you...

customaware

Hi Kklosson,

Unfortunately that won't work for many situations.

That function requires a number of days to add or subtract and hence
fails with different number of days in a month and different number of
days in a year (leap year)

In other systems I have used, a similar function takes 4 arguements such
as DATE_ADD(StartDate,DaysToAdd,MonthsToAdd,YearsToAdd) which would
make the problem trivial.

RocketRod

As kklosson said Date_Add should do it. Try this for the monthly version. Haven't tested it! If it works you can work out yearly version.

Cheers Rod

Enddate=DATE_ADD(Startdate,Last_Day_of_Month(StartDate)-1)

customaware

Thanx Guys,

It is actually.....

Enddate=DATE_ADD(Startdate,DAY_OF_MONTH(LAST_DAY_OF_MONTH(StartDate))-1)

It actually fails for the 31st January..... gives 2nd March instead of 28th February and any month where the number of days in the month is more than the next month.. ie. April. which is understandable.

I will frig around and sort out a workaround for that.

Gabbitas

Hi Eagles9999,

Did you ever work this one out?

I need to add 1 year to a datestamp but can't figure it out! This should be easy.....I bet it's looking straight at me!

Thanks

RLJB

Bit late for the earlier posts but the below will do what you wanted Eagles9999, works for leap years also.

BOName.EndDateMonthly=DATE_ADD(MONTH_ADD(BONameStartDate,1),-1)
BOName.EndDateAnnually=DATE_ADD(MONTH_ADD(BOName.StartDate,12),-1)

Gabbitas,

BoName.AnniversaryDate=MONTH_ADD(BoName.StartDate,12)

That'll add a year to a date attribute.

Cheers

Gabbitas

Hi RLJB,

Thanks for your reply. I did get there in the end - I knew it was easy enough but just kept blanking :roll: