add/minus months function

mickboland · Post by **mickboland** » Sun Mar 19, 2006 11:23 pm

Is there a standard function to add or take away months from a date.

Sunshine2323 · Post by **Sunshine2323** » Sun Mar 19, 2006 11:56 pm

Can be done using OCONV, ICONV

Check this post

mickboland · Post by **mickboland** » Mon Mar 20, 2006 12:12 am

how?

How would you take 4 months from the date 20/01/06 (ie: return a date of 20/09/05).

ArndW · Post by **ArndW** » Mon Mar 20, 2006 12:39 am

Your date is coming in as a string in the format dd/mm/yy. You don't care about the dd portion and can do a substring on the month and year and perform you math on those. Even though it could be done as a single line in a derivation, a small routine would be best:

Code: Select all

Routine Subtract4MonthsfromDate(InDate)

InDay = InDate[1,2]
InMonth = InDate[3,2]
InYear = InDate[7,2]

OutMonth = InMonth - 4
IF OutMonth < 1
THEN
   OutMonth = 12 + OutMonth
   OutYear  = OutYear - 1
END
Ans = InDay:'/':OutMonth:'/':OutYear

rleishman · Post by **rleishman** » Mon Mar 20, 2006 12:52 am

Don't know how to do it with iconv/oconv alone, but you could write your own routine without too much difficulty.

- Extract 3 numeric vars from your date: YYYY, MM, and DD. You can use iconv/oconv for this - search for "d code" in the online help.
- Add n/12 to YYYY
- Add mod(n, 12) to MM
- If MM>12, YYYY+=1 and MM-=12
- Reassemble date as YYYY-MM-01 and convert to internal date format
- Add DD days.
- If the final date is > MONTH.LAST(YYYY-MM), then set it to MONTH.LAST(YYYY-MM).

tcj · Post by **tcj** » Mon Mar 20, 2006 12:55 am

Don't forget to check for leap year :D

rleishman · Post by **rleishman** » Mon Mar 20, 2006 3:55 pm

Doesn't MONTH.LAST do leap-year checking?

tcj · Post by **tcj** » Mon Mar 20, 2006 6:14 pm

Yeah it does.

My post was for ArndW.

rasi · Post by **rasi** » Mon Mar 20, 2006 7:13 pm

Hi mick

How would you take 4 months from the date 20/01/06 (ie: return a date of 20/09/05).

You cannot simply substract 4 months from the given date always. Because value "30/06/05 " will give you 30/02/05 which is not a valid date. You have to consider day part as well for the above value.

It's always a good practice to check whether the returned value is valid date or not. Have a business logic defined for cases like this either you take the last day of the month or first day of next month etc.

Thanks

ray.wurlod · Post by **ray.wurlod** » Mon Mar 20, 2006 7:58 pm

Didn't I leave you a set of date handling functions when I was at your site? AddMonths would be one of them. The offset can be positive or negative.

mickboland · Post by **mickboland** » Mon Mar 20, 2006 8:05 pm

Ray,

No date functions!!

ArndW · Post by **ArndW** » Tue Mar 21, 2006 2:26 am

tcj wrote:Don't forget to check for leap year :D

I would have done that in a premium post

My favorite was is to do an ICONV on that output date and let the routine "clean it up" and tell me so with the return status code.