Thursday, August 8, 2013

Calculating end of month

Although the subject matter of this post may seem simple to the more experienced RPG black belts, I am creating this post in response to questions I have receieved recently from two collegues. I was asked if there was an "easy" way to determine the date of the end of the month entered. They both had examples in RPG III and were sure that there must be simpler way.

In RPG III I could determine the date of the last day of the month like this:

     E                    DAYS   12  12  2 0
      *
     IINPUT       DS
     I                                        1   20MM
     I                                        3   40DD
     I                                        5   80YEAR
      *
     C                     MOVE *DATE     INPUT
     C           MM        IFNE 2
     C                     Z-ADDDAYS,MM   DD
     C                     ELSE
     C           YEAR      DIV  400       X       40
     C                     MVR            X
     C           X         IFEQ *ZERO
     C                     MOVE '29'      DD
     C                     ELSE
     C                     MOVE '28'      DD
     C                     ENDIF
     C                     ENDIF

**                      
31XX31303130313130313031

In RPGLE/RPG IV it is far easier using a date field and built in functions, BIFs.

In this example I am going to take the current date, use it to calculate the date for the end of this month, and present the date in USA format. I could code it like this:

01 D wkDate          S               D
02 D Output          S             10
03  /free
04   wkDate = %date() - %days((%subdt(%date():*days) - 1))
05                    + %months(1)
06                    - %days(1) ;
07   Output = %char(wkDate:*usa/) ;

Line 4 is where the "action" starts.

The first BIF is %DATE, which gives me the current date, for today it is 2013-08-08 (I am giving the date in *ISO format to make it easier for everyone to understand as our international readers could use a different date format to the USA).

From that I subtract the (current number of days - 1) to get the first of the month. To do this I use the %SUBDT BIF to extract the number of days, *days, from today's date, %DATE(). This gives me the number 8. I subtract one from that to give me 7. Then using the %DAYS BIF I subtract that number from the today's date. Which gives me:
(today - (day_of_month - 1) days) = 2013-08-01.

I add one month to the date using the %MONTHS BIF, line 5. Therefore, the date is now 2013-09-01.

Finally I subtract one day from the date, line 6, using the %DAYS BIF. This gives me 2013-08-31.

I then convert, on line 7, the date to an *USA format (MMDDYYYY) alphanumeric field with date separator character of '/'. In our example the field Output would contain the value '08/31/2013'.

As you can see this is a lot smarter way of calculating the last day of the month versus the way we had to do it using RPG III.

You can learn more about these BIFs on the IBM website:

 

This article was written for IBM i 7.1, and it should work with earlier releases too.


Friday August 9, 2013:   I was sent a number of interesting messages on how this could be done using SQL. Therefore, I created a new post, Calculating end of month part II, for the best example.


9 comments:

  1. Nothing wrong with the RPG II/III method. Except you must divide the year by 400, so as to detect leap years which end in 00.

    In any database, dates should be stored in ISO format to ease sorting. Math or manipulations are thus simplified, and indeed callable or service modules may be created for common tasks.

    A date may be then reformatted just before display or print to whatever a Locale determines.

    JMGJ

    ReplyDelete
    Replies
    1. The two main applications I use on a daily basis in different ways. The first stores the date in CYMD format. The second stores it as four fields: Century, Year, Month, and Day.

      But that is not the strangest. That has to go to Infinium's HYF (Hundred Year Format) date. Which is a count of the number of days since January 1, 1900. They had an API that you would call to transform the HYF into a more recognizable date.

      Delete
    2. Infinium's hundred year calculation program is still in use, believe it or not. We thought it was brilliant in the 1980s. It also returns last day of the current month and whether or not this is a leap year.

      Delete
    3. The place I did the most with Infinium had a problem with the HYF. They had a pensioner who was born in 1899. As the HYF does not handle dates before January 1, 1900, we had a tax reporting issue.

      We entered him into the HR/Payroll database with a birth date of Jan 1 1900 as we awaited a resolution from Infiniuum. Unfortunately he died before Infinium could give us a solution.

      Delete
  2. Oddly enough, I've seen third party products that went through the process of an RPG III to IV conversion, but never bothered to streamline the date arithmetic or update the database. In certain areas such as financials, they could have cut their lines of code by 20%.

    Chris Burns, Sr.

    ReplyDelete
  3. Here is a nice piece of code for Infinium users to replace several lines of code and an external call....

    hyfDays=%diff(someDate:%date('12/31/1899':*usa):*days);

    -kh

    ReplyDelete
  4. I like to develop clear concise code. With all due respects to my colleagues, I don't like code that has 1899 in it or divide by 400. I don't like convoluted assignment(eval)statements with all manners of buried built in functions. I don't like IBM created crazy cryptic things with their date logic like *iso or *iso0. Every have to look at that crap at 3AM? I don't like code that if the format of the date changes and the routine will be blown out of the water. Some junior programmer is on holiday support and does not know what to do with a big convoluted assignment(eval) statement that blows up. A program should run efficiently, but it should be efficient to support. I know there are people that are going to hate this solution, but Keep It Simple Sonny. I do return the date in mm/dd/yyyy, but the return date could be in a different format. Remember this routine will not blow up it the format of the input date changes. They key to this solution is simplicity and the %subt


    Hdatfmt
    Dinputdate s d datfmt(*mdy) inz(d'02/15/12')
    D EndOfMonth s 2s 0 dim(12) ctdata perrcd(12)
    D Leap S 2s 0
    D Month s 2s 0
    D LastDay s 2s 0
    D Year s 4s 0
    D LastDayOfMonth s 8s 0
    /free
    Month = %subdt(inputdate :*months);
    LastDay = EndOfMonth(Month);
    Year = %subdt(inputdate:*years);
    Leap = %rem(Year:4);
    If Leap = 0 and month = 2;
    LastDay = LastDay + 1;
    Endif;

    LastDayOfMonth =(Month * 1000000) + (lastday * 10000) + Year;

    *inlr = *On;

    **
    312831303130313130313031

    This routine will work for MDY,*YMD and *ISO dates.

    ReplyDelete
  5. John, probably no one likes code with hard code like 1899 in it, but do you have a no-hard-code alternative that would work for the Infinium hundred-year date format?
    Please share!
    -kh

    ReplyDelete
  6. Interesting methods.
    I create a data structure that has the ISO date, but also defines the days field in it.
    - Put the current date into the ISO date
    - Set the DAY field to 1 (you now have the first day of this month)
    - AddDur 1:*M to the ISO date (you now have the first day of next month)
    - SubDur 1:*D from the ISO date (you now have the last day of this month)

    Obviously you can substitute any date for the current date, as you need.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.