Calculate Begin and End Dates for Any Month 
Programming  RPG  
Written by Sam Lennon  
Tuesday, 01 April 2008 20:00  
Need to calculate the dates of the first and last days of last month or any month? See how easy it is in RPG IV.
It's fairly common that you need to calculate the dates of the first and last days of a month to print in report headings, show on screens, or select data. It is usually last month, but it could be next month or the last three months or whatever the customer requests. Date arithmetic in RPG IV makes it very easy to calculate past and future dates and date ranges. There are three simple rules, and it doesn't matter how you store dates.
Without RPG IV, typical "last month" logic might go something like this: To get the first of last month, split out year, month, and day using a data structure, force the day to 01, subtract one from the month, and if the result is zero, set month to 12 and subtract one from the year. To get the last day of last month, consult an array based on month, except if the month is 02; then you also have to figure out if this is a leap year, which is when the year is divisible by 4, but not divisible by 100, but is divisible by...and at that point my head begins to hurt! But help is at hand: RPG IV date arithmetic eliminates all this complexity. You can do it all in just two lines of code.
It's true that RPG IV date arithmetic must take place on date data types. What if you are saddled with legacy dates in packed or character fields? Not a problem; just check a previous article ("RPG IV Legacy Dates Cheat Sheet"), where I showed how easy it is to convert legacy dates to true dates and convert true dates back again, one statement for each direction. So with legacy dates, you might be up to four lines of code. Please keep reading.
First, I need to get into a little theory on how RPG IV handles date arithmetic.
RPG IV date arithmetic allows three durations to be added or subtracted from a date: days, months, and years. The logic in this article relies on the days and months durations. For clarity, I'm showing all example dates in ISO format, yyyymmdd.
Adding/Subtracting Days in RPV IV
Add or subtract a duration of one or more days to a date in RPV IV and you always get a valid date. The underlying logic is that the day always changes and the month and year change if necessary.
Examples: •· 20080114 plus two days gives 20080116. (Only the day changed.) •· 20080229 plus one day is 20080301. (The day and month changed. In addition, note that RPG IV knows that 2008 is a leap year.) •· 20071231 plus one day is 20080101. (Day, month, and year changed.)
Adding/Subtracting Months
Durations in months are a bit trickier, because there isn't always the same number of days in a month. What result would you expect if you add one month to 20080101? I think most people would expect 20080201 and that is what RPG IV provides.
But what should the result be if you add one month to 20080130? Hopefully no one is going to expect 20080230, because that is an invalid date, even in a leap year. Some might expect 20080301, given that January, where we started, has 31 days. RPG IV actually gives 20080229. Why?
The logic with month durations is that the month always changes, the year changes if necessary, and the day doesn't change unless the result would be an invalid date, in which case the day is adjusted to the last day of the month. So, if you add one month to 20080129, 20080130, or 20080131, the result is the same, 20080229.
This leads to some perhaps nonintuitive situations where adding and subtracting the same duration doesn't get you back to where you started. For example, 20080131 plus one month and then minus one month results in 20080129.
Adding/Subtracting Years
The year duration rule is similar to the month rule. The year always changes, the month does not change, and the day changes only if the result is an invalid date. 20080229 plus one year is 20090228.
Enough of theory. How do you use date arithmetic to calculate month date ranges?
Rule 1 When using month durations, always work from the first of the month. As I'm sure you noticed above, the discrepancies come with dates at the end of the month.
Rule 2 To get to the first day of the month for any date, take the day of the month, subtract 1, and then subtract that many days from the date.
For example, if the date is 20080302, then the day of the month is 02, so the start of the month is calculated as 20080302 minus 02  1 days, which results in 20080301. Similarly, if the date is 20080321, then the start of the month is 20080321 minus 21  1 days. This formula holds true for any date.
Rule 3 The first day of a month minus one month gives you the first day of the previous month. The first day of a month, minus one day, gives you the last day of the previous month. And that's all that is required to find the date range for last month.
Practical RPG IV Code
Following is the RPG code to calculate first and last dates for last month, done step by step in four lines of code for clarity. If you need more details about any of the Builtin Functions (BIFs) used, check them in the RPG reference.
D A_Date s d D WorkDays S 3P 0 D MthBeg s d D PrevMthEnd s d D PrevMthBeg s d /free
A_Date = %date('20080317': *iso); WorkDays = %subdt(A_Date: *days); MthBeg = A_Date  %days(WorkDays 1); PrevMthEnd = MthBeg  %days(1); PrevMthBeg = MthBeg  %months(1);
Field A_Date is any date value. You could substitute %DATE() to use the current date. I'm setting it to St. Patrick's Day in 2008 for demonstration purposes.
The four steps/lines are straightforward: 1. WorkDays is calculated using the %subdt BIF to extract the day of the month: 17. 2. Subtract (WorkDays  1) days from the A_Date to get MthBeg, the beginning of the month: 20080301. 3. Subtract 1 day from MthBeg to get the PrevMthEnd, the last day of the previous month: 20080229. 4. Subtract 1 month from MthBeg to get PrevMthBeg, the first day of the previous month: 20080201.
You can just as easily do the calculation in two statements as I mentioned, without any intermediate fields:
PrevMthEnd = A_Date  %days(%subdt(A_Date: *days)); PrevMthBeg = PrevMthEnd + %days(1)  %months(1);
It is not as intuitive because it isn't so obvious that we are doing all month durations from the first of the month.
Turns out to be simple and obvious, doesn't it, once someone points it out? Using this technique, starting with any date, you can find out the first and last dates of any month before it or after it.
SQL
Incidentally, if you are into SQL, the same rules work there. Here's equivalent SQL:
A_Date  day(A_Date) days as PrevMthEnd, A_Date  (day(A_Date) + 1) days  1 months as PrevMthBeg
Use these constructions in a WHERE clause to select data. Substitute curdate() for A_Date if you want to calculate last month.  


Last Updated on Thursday, 27 March 2008 12:04 