Determine the end of month using CL

The idea for this post came from several people who asked me the same question: "How to find the date of the end of the month? only using CL". When I quizzed them they explained that this was asked during an interview. If anyone asks me for help I always ask them how they would do it, and they all had pretty much the same solution, but struggled with how to determine if February should have 28 or 29 days. They asked how I would have done it. I came up with two solutions that I will share here.

The first version is very similar to what they can come up with. In this example the date is in *DMY format.

01 PGM 02 DCL VAR(&QDATE) TYPE(*CHAR) LEN(6) 03 DCL VAR(&MONTH) TYPE(*CHAR) LEN(2) 04 DCL VAR(&MONTHEND) TYPE(*CHAR) LEN(8) 05 RTVSYSVAL SYSVAL(QDATE) RTNVAR(&QDATE) 06 CHGVAR VAR(&MONTH) VALUE(%SST(&QDATE 3 2)) 07 SELECT 08 WHEN COND((&MONTH = '01') + *OR (&MONTH = '03') + *OR (&MONTH = '05') + *OR (&MONTH = '07') + *OR (&MONTH = '08') + *OR (&MONTH = '10') + *OR (&MONTH = '12')) + 09 THEN(CHGVAR VAR(%SST(&QDATE 1 2)) VALUE('31')) 10 WHEN COND((&MONTH = '04') + *OR (&MONTH = '06') + *OR (&MONTH = '09') + *OR (&MONTH = '11')) + 11 THEN(CHGVAR VAR(%SST(&QDATE 1 2)) VALUE('30')) 12 OTHERWISE CMD(DO) 13 CHGVAR VAR(%SST(&QDATE 1 2)) VALUE('29') 14 CVTDAT DATE(&QDATE) TOVAR(&QDATE) TOSEP(*NONE) 15 MONMSG MSGID(CPF0000) + EXEC(CHGVAR VAR(%SST(&QDATE 1 2)) + VALUE('28')) 16 ENDDO 17 ENDSELECT 18 CVTDAT DATE(&QDATE) TOVAR(&MONTHEND) TOFMT(*YYMD) + TOSEP(*NONE) 19 ENDPGM

Line 5: I retrieve the current date from the system value QDATE.

Line 6: I substring the month number from the current date using the substring built in function, %SST.

Line 7: Rather than use multiple if statements I can use a select command to make the code more readable.

Lines 8 and 9: For those months with 31 days I change the first two positions of the date field with 31.

Lines 10 and 11: The same goes for the months with 30 days.

Line 12: What about February? This is the part the questioners could not come up with an easy solution for.

Line 13: I change the day number to 29.

Line 14: I can use Convert Date command, CVTDAT, to validate 29th February. I use the same variable for the from and to date parameters, DATE and TOVAR, and I do not want any date separator characters in the to date, TOSEP(*NONE).

Line 15: If 29th February 2017 is not a valid date, which it is not, then the CVTDAT would error. This Monitor Message command, MONMSG, "catches" the error, and changes the day number to 28.

Line 18: I have added this as I would like the final date to be YYYYMMDD rather than DDMMYY, as you cannot sort dates if they are in *DMY. The to format has to be *YYMMD as *ISO inserts date separators whether I want them or not.

If I had been asked to do this in RPG I would use an embedded SQL statement:

dcl-s MonthEnd date ; exec sql SET :MonthEnd = LAST_DAY(CURRENT_DATE) ;

So how could I put that into a CL program?

Those of you who are regular readers of this site know that I like the Run SQL command, RUNSQL, in CL. Alas, RUNSQL does not support all the types of SQL statements that RPG does, for example the SET I used in my RPG example is not supported by the RUNSQL command.

After some attempts I came up with the following:

01 PGM 02 DCLF FILE(QTEMP/WORKFILE) 03 DLTF FILE(QTEMP/WORKFILE) 04 MONMSG MSGID(CPF0000) 05 RUNSQL SQL('CREATE TABLE QTEMP.WORKFILE + 06 (MONTHEND) + 07 AS (SELECT LAST_DAY(CURRENT_DATE) + 08 FROM SYSIBM.SYSDUMMY1) + 09 WITH DATA') + 10 COMMIT(*NC) 11 RCVF 12 CVTDAT DATE(&MONTHEND) TOVAR(&MONTHEND) + FROMFMT(*ISO) TOFMT(*YYMD) TOSEP(*NONE) 13 ENDPGM

Line 2: I am declaring a file. As this is a work file it will exist in QTEMP, where all work files should.

Line 3: If the file already exists, delete it. I could have used DROP TABLE in a RUNSQL statement, but wanted to show that the Delete file command, DLTF, deletes SQL created tables as well as DDS files.

Line 4: If the file does not exist I do not want the program to error, hence the Monitor Message command.

Lines 5 – 10: This may look complicated, but it is not. I am going to create a table on the fly. The new table will be created in QTEMP, line 5, with one field called MONTHEND, line 6. Into this table I am inserting a value from another table/file, lines 7 and 8. I am using the LAST_DAY function, as I did in my RPG example, to get the last day of today's month. SYSIBM.SYSDUMMY1 is, as its name suggests a dummy. It does exist, and it is used like this as good SQL practice to show that whatever is selected is not from this table/file. WITH DATA, line 9, indicates that data from the select is inserted into the new table.

Line 11: Now I have the end of the current month in my work table I need to read the table. The Receive File command, RCVF, does that. As only one file is defined within the program I do not have to give the file or record format name. As I know there is going to be a row/record within the table I am not checking to see if end of file is encountered when I do this.

Line 12: As I did in the other example CL program I am converting the date to *YYMMDD. The value retrieved from the table is in *ISO format, therefore, I do have to give that in the FROMFMT parameter of the command.

Personally I like the second example better than the first. If I was on the interview and was challenged that this approach is not strictly CL I would argue that the RUNSQL is part of CL and this approach is valid.

Before I get messages from people telling me that helping people with interview questions is "cheating", in my opinion it is not as this is a learning experience for those who did not know how to do this.

 

You can learn more about this from the IBM website:

• CVTDAT command
• RCVF command
• %SST built in function

 

This article was written for IBM i 7.3, and should work for earlier releases too.

---

Addendum

Thank you to everyone how has posted their suggestions in the comments, below. One I really liked was the one made by Ringer, counting down from 31 to 28 and using the CVTDAT to validate if the date is good, see here. He did not give example code so I thought I would show my interpretation:

01 PGM 02 DCL VAR(&QDATE) TYPE(*CHAR) LEN(6) 03 DCL VAR(&MONTHEND) TYPE(*CHAR) LEN(8) 04 RTVSYSVAL SYSVAL(QDATE) RTNVAR(&QDATE) /* Date in DMY */ 05 CHGVAR VAR(%SST(&QDATE 1 2)) VALUE('31') 06 CVTDAT DATE(&QDATE) TOVAR(&QDATE) TOSEP(*NONE) 07 MONMSG MSGID(CPF0555) EXEC(DO) 08 CHGVAR VAR(%SST(&QDATE 1 2)) VALUE('30') 09 CVTDAT DATE(&QDATE) TOVAR(&QDATE) TOSEP(*NONE) 10 MONMSG MSGID(CPF0555) EXEC(DO) 11 CHGVAR VAR(%SST(&QDATE 1 2)) VALUE('29') 12 CVTDAT DATE(&QDATE) TOVAR(&QDATE) TOSEP(*NONE) 13 MONMSG MSGID(CPF0555) EXEC(DO) 14 CHGVAR VAR(%SST(&QDATE 1 2)) VALUE('28') 15 ENDDO 16 ENDDO 17 ENDDO 18 CVTDAT DATE(&QDATE) TOVAR(&MONTHEND) TOFMT(*YYMD) + TOSEP(*NONE) 19 ENDPGM

Remember that in this example the system date format is *DMY.

Line 5: Change the day's part of the date to 31.

Line 6: Validate the date using the CVTDAT command. I do not have to give the From and To date formats as I am just using the default, *JOB, but I do not want date separators inserted into the To date.

Line 7: Message CPF0555 only happens if the date is invalid. If the date is valid the logic goes down to line 17.

Lines 8 – 10: As the date using 31 was not valid let me try with 30.

Lines 11 – 13: The only month that will reach here is February. Now to determine if this is a leap year using 29 as the day of the month.

Line 14: If all the above has failed then this month has to February and it is not a leap year, therefore, the end of the month is the 28th.