Sometimes a coincidence happens where two people ask for similar things that make me think that their question could become the subject for a post on this blog. Someone, via the internet, asked me if it was possible in RPG to write a program that would calculate the last Friday for every month. The following week I received a request, at my work, to run a report on the first Monday of every month. When two things like this come together it is a good subject to discuss.
Over the years I have found many ways to determine the day of the week. I have used CEE APIs (CEEDAYS and CEEDYWK), calculations of varying complexity (especially in RPGIII), and one software package (I will not name) had a file that had a record for every date from 1900 to 2100 with fields containing various date formats, week numbers, and the day name. For this scenario I wanted something simple, you know KISS (Keep It Simple Simon).
Fortunately there is a very simple way to get these days of the week using SQL date functions.
My two examples programs are very similar. In both of these programs I am going to calculate the days for 2016. I think the program to determine the first Mondays is the simpler of the two, therefore, I will start with that one:
01 dcl-s wkDate date ; 02 dcl-s wkTStamp timestamp ; 03 dcl-s wk8A char(8) ; 04 dcl-s Month packed(2) ; 06 dcl-s wkDay packed(1) ; 07 for Month = 1 to 12 ; 08 wk8A = %editc(Month:'X') + '/01/16' ; 09 exec sql SET :wkDay = DAYOFWEEK(:wk8A) ; 10 if (wkDay = 2) ; // Is Monday 11 wkDate = %date(wk8A:*mdy/) ; 12 else ; 13 exec sql SET :wkTStamp = NEXT_DAY(:wk8A,'MON') ; 14 wkDate = %date(wkTStamp) ; 15 endif ; 16 dsply ('First Monday of the month = ' + %char(wkDate:*iso-)) ; 17 endfor ; 18 *inlr = *on ;
Fixed format definitions can be found here.
Lines 1 – 6 define the variables I will be using. The wkDay variable has to be packed as the SQL statement expects a packed decimal value for the result. The program will not compile if it is defined as signed numeric.
As I am only going to demonstrate this for a year, 2016, I have a FOR group to execute 12 times, line 7.
I use the incremental value to make an alphanumeric representation of the first day of the months in 2016, line 8. Note that as I am in the USA my alphanumeric representation of the date, wk8A, is in *MDY format. You might want to change this part of the program for your country’s preferred date format.
I get which day of the week my date is using the DAYOFWEEK function, on line 8. The days are returned as an integer where 1 is Sunday and 6 is Saturday.
If the date is a Monday, line 10, then I convert my alphanumeric representation of a date to a date field, wkDate, line 11.
If it is not a Monday then I can find the next Monday using the NEXT_DAY, line 13. For the first parameter of the function I used wk8A, my alphanumeric variable, and the second the day I want to find. I have discovered that as I have used a alphanumeric variable in the function the value returned is timestamp, wkTStamp, which I convert to a date, wkDate, on line 14.
The DSPLY operation, line 16, will show me the dates I calculated:
DSPLY First Monday of the month = 01/04/2016 DSPLY First Monday of the month = 02/01/2016 DSPLY First Monday of the month = 03/07/2016 DSPLY First Monday of the month = 04/04/2016 DSPLY First Monday of the month = 05/02/2016 DSPLY First Monday of the month = 06/06/2016 DSPLY First Monday of the month = 07/04/2016 DSPLY First Monday of the month = 08/01/2016 DSPLY First Monday of the month = 09/05/2016 DSPLY First Monday of the month = 10/03/2016 DSPLY First Monday of the month = 11/07/2016 DSPLY First Monday of the month = 12/05/2016
The program to determine the last Friday is not that different, so much so I am not going to include the definitions as they are the same:
05 for Month = 1 to 12 ; 06 wk8A = %editc(Month:'X') + '/01/16' ; 07 exec sql SET :wkDate = LAST_DAY(:wk8A) ; 08 exec sql SET :wkDay = DAYOFWEEK(:wkDate) ; 09 if (wkDay <> 6) ; // Is not Friday 10 wkDate -= %days(7) ; 11 exec sql SET :wkDate = NEXT_DAY(:wkDate,'FRI') ; 12 endif ; 13 dsply ('Last Friday of the month = ' + %char(wkDate:*iso-)) ; 14 endfor ;
I need to determine the last day of the month, therefore, I use the LAST_DAY function which uses my alphanumeric variable for the input parameter, and gives me the date of the last day of the month in a date variable, line 7.
I need to determine which day of the week the last day of the month is, line 8.
If the last day is not a Friday, line 9, then I subtract a week from the date, line 10. Then I use the NEXT_DAY function as I did in my previous example. This time my result can be a date variable, rather than a timestamp.
I use the DSPLY operation code to show the results:
DSPLY Last Friday of the month = 2016-01-29 DSPLY Last Friday of the month = 2016-02-26 DSPLY Last Friday of the month = 2016-03-25 DSPLY Last Friday of the month = 2016-04-29 DSPLY Last Friday of the month = 2016-05-27 DSPLY Last Friday of the month = 2016-06-24 DSPLY Last Friday of the month = 2016-07-29 DSPLY Last Friday of the month = 2016-08-26 DSPLY Last Friday of the month = 2016-09-30 DSPLY Last Friday of the month = 2016-10-28 DSPLY Last Friday of the month = 2016-11-25 DSPLY Last Friday of the month = 2016-12-30
By using SQL these programs are simpler to understand than they would have been written in RPG, and especially RPGIII.
I had previously written about these SQL date functions and described them more detail:
This article was written for IBM i 7.2, and should work for earlier releases too.
Fixed format definitions
D wkDate S D D wkTStamp S Z D wk8A S 8 D Month S 2 0 D wkDay S 1 0 /free