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, yyyy-mm-dd.

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:

•·                    2008-01-14 plus two days gives 2008-01-16. (Only the day changed.) 

•·                    2008-02-29 plus one day is 2008-03-01. (The day and month changed. In addition, note that RPG IV knows that 2008 is a leap year.)

•·                    2007-12-31 plus one day is 2008-01-01. (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 2008-01-01? I think most people would expect 2008-02-01 and that is what RPG IV provides.

But what should the result be if you add one month to 2008-01-30?  Hopefully no one is going to expect 2008-02-30, because that is an invalid date, even in a leap year. Some might expect 2008-03-01, given that January, where we started, has 31 days. RPG IV actually gives 2008-02-29. 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 2008-01-29, 2008-01-30, or 2008-01-31, the result is the same, 2008-02-29.

This leads to some perhaps non-intuitive situations where adding and subtracting the same duration doesn't get you back to where you started. For example, 2008-01-31 plus one month and then minus one month results in 2008-01-29.

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. 2008-02-29 plus one year is 2009-02-28.

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 2008-03-02, then the day of the month is 02, so the start of the month is calculated as 2008-03-02 minus 02 - 1 days, which results in 2008-03-01. Similarly, if the date is 2008-03-21, then the start of the month is 2008-03-21 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 Built-in 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('2008-03-17': *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: 2008-03-01.

3.                  Subtract 1 day from MthBeg to get the PrevMthEnd, the last day of the previous month: 2008-02-29.

4.                  Subtract 1 month from MthBeg to get PrevMthBeg, the first day of the previous month: 2008-02-01.

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.