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 |
Awesome examples! Another good topic that is date/time related, is dealing with durations. How to determine and display durations between timestamps.
ReplyDeleteHow about this post here?
DeleteVery nice use of sql :-)
ReplyDeleteI have one small comment
: in denmark ( and lot of europe) week starts monday so we would use weekday_iso and friday would be 5, not 6 :-) your code would work but would confuse people when they see 6 as friday :-)
A good point. This is why I always recommend if you take any code from this site you should test it to ensure it works in your environment.
Deleteu can use : the sysval QDAYOFTHEWEEK : Example:
ReplyDeleteRTVSYSVAL SYSVAL(QDAYOFWEEK) RTNVAR(&DWEEK)
IF COND(&DWEEK *NE '*SAT') THEN(GOTO CMDLBL(ENDPGM))
But that would not tell me this is the first Monday or last Friday of this month.
DeleteIf u can send date this RPG give u the dayoftheweek:
Deleted Day_Of_Week s 1p 0
D My_date S D inz(d'2011-03-15')
D My_date2 S 8s 0 inz(20110415)
/free
// *Mon = 0 ... Sun = 6
Day_Of_Week = %rem(%diff(%date():d'0001-01-01':*days):7);
Day_Of_Week = %rem(%diff(My_date:d'0001-01-01':*days):7);
Day_Of_Week = %rem(%diff(%date(My_date2):d'0001-01-01':*days):7);
SELECT;
WHEN Day_Of_Week = 0;
dsply '*MON';
WHEN Day_Of_Week = 1;
dsply '*TUE';
WHEN Day_Of_Week = 2;
dsply '*WED';
WHEN Day_Of_Week = 3;
dsply '*THU';
WHEN Day_Of_Week = 4;
dsply '*FRI';
WHEN Day_Of_Week = 5;
dsply '*SAT';
WHEN Day_Of_Week = 6;
dsply '*SUN';
OTHER;
ENDSL;
/end-free
I am not saying you cannot do what I did in RPG. I have done so in the past in both RPGIII & RPGLE. My post was written to show how you can do things like this in SQL.
DeleteEverthing kan be done in rpg, :-) but i think the point here is how easy and nice it can be done with db2.
DeleteJan is correct. We are lucky to work with a system that allows to do the same thing in multiple ways.
DeleteAnd beeing able to choose the method that makes most sence to solve the problem. Beeing a developer for many years you realize that readabillity of the code is essential.
DeleteJan is correct (again). We should all be looking for the best tool for the job to make it easier for others to understand our code (or myself in 6 months time when I try & remember why I did it that way)
DeleteYes ! Thats what makes a god developer :-)
DeleteExcellent work, Simon!
ReplyDeleteAs a pure SQL example for last Friday of the month for the next 1200 months, starting from an epoch of 1980.
ReplyDeletehttp://pastebin.com/vqyC1xcU
This creates a first of the month CTE for the next 1200 months then applies your method to each row. I'm pretty sure that someone could figure out a better method that is pure SQL but just wanted to throw this out there.
May be someone is interested, here an other solution.
ReplyDeleteFirst Monday and last Friday are both determined directly with SQL and RPG is only used for displaying the result:
DCL-S GblStartDate Date(*ISO) inz(D'2015-12-31');
DCL-S GblEndDate Date(*ISO) inz(D'2016-12-31');
DCL-DS GblDSCsrC01 qualified Inz;
FirstMonday Date(*ISO);
LastFriday Date(*ISO);
End-Ds;
********************************************************************************
*INLR = *On;
Exec SQL Declare CsrC01 Cursor For
With MonthEnd (LastDay)
as ( Values(Date(:GblStartDate))
Union All
Select Add_Months(LastDay, 1)
from MonthEnd
where LastDay < :GblEndDate)
Select Next_Day(LastDay, 'MON'),
Next_Day(Add_Months(LastDay, 1) - 7 Days, 'FRI')
From MonthEnd;
Exec SQL Open CsrC01;
DoU 1=0;
Exec SQL Fetch Next From CsrC01 into :GblDSCsrC01;
If SQLCODE = 100 or SQLCODE < *Zeros;
Exec SQL Close CsrC01;
Leave;
EndIf;
Dsply ('First Monday Of the Month ' +
%Char(GblDSCsrC01.FirstMonday: *ISO));
Dsply ('Last Friday Of the Month ' +
%Char(GblDSCsrC01.LastFriday: *ISO));
EndDo;
Return;
Birgitta
Simon,
ReplyDeleteSince you determine the day of the week for either the first day or the month or the last day of the month, you don't need the NEXTDAY function (unless your point to give an example of it). For figuring the first Monday of the month, you can use (I can't do monospaced here):
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 = 9 - DAYOFWEEK(:wk8A) ;
10 if (wkDay >= 7) ; // Is Sunday or Monday
11 wkDay -= 7 ;
12 endif ;
13 wkDate += %days(wkDay) ;
14 dsply ('First Monday of the month = ' +
%char(wkDate:*iso-)) ;
15 endfor ;
16 *inlr = *on ;
Determining the last Friday:
Delete08 exec sql SET :wkDay = DAYOFWEEK(:wkDate) + 2 ;
09 if (wkDay >= 8) ; // Is Friday or Saturday
10 wkDay -= 8 ;
11 endif ;
12 wkDate -= %days(wkDay) ;
I don't have a way to test these so I'll turn them over to you to do with as you wish.
I am dropping most of my IBM i groups on LinkedIn. It's clear that I live in my little world with my two clients where things are nice and cozy.
Bruce
I always create new programs as type sqlrpgle. Can use traditional rpg and embedded SQL, both of best worlds
ReplyDelete