add/minus months function
Posted: Sun Mar 19, 2006 11:23 pm
Is there a standard function to add or take away months from a date.
Can be done using OCONV, ICONV
Check this post
Page 1 of 1
Posted: Sun Mar 19, 2006 11:56 pm
Posted: 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).
How would you take 4 months from the date 20/01/06 (ie: return a date of 20/09/05).
Posted: 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
Posted: 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).
- 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).
Posted: Mon Mar 20, 2006 12:55 am
Don't forget to check for leap year :D
Posted: Mon Mar 20, 2006 3:55 pm
Doesn't MONTH.LAST do leap-year checking?
Posted: Mon Mar 20, 2006 6:14 pm
Yeah it does.
My post was for ArndW.
My post was for ArndW.
Posted: Mon Mar 20, 2006 7:13 pm
Hi mick
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
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.How would you take 4 months from the date 20/01/06 (ie: return a date of 20/09/05).
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
Posted: 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.
Posted: Mon Mar 20, 2006 8:05 pm
Ray,
No date functions!!
No date functions!!
Posted: Tue Mar 21, 2006 2:26 am
I would have done that in a premium posttcj wrote:Don't forget to check for leap year :D
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.