I was asked how to make sure if I added 1 month to the last day of this month I would get the end of next month. If I take a date from a month and add one month to it I get the end of the next month.
In RPG if I add one month to January 31 I get February 28, the last day of the next month.
wkDate = d'2022-01-31' + %months(1) ; dsply wkDate ; DSPLY 2022-02-28 |
When I add one month to February 28 I do not get the last day of March, I get March 28.
wkDate = d'2022-02-28' + %months(1) ; dsply wkDate ; DSPLY 2022-03-28 |
The same is true using SQL. If I add one month to January 31 I get February 28.
VALUES DATE('2022-01-31') + 1 MONTH ;
00001
----------
2022-02-28
|
When I add one month to February 28 I get March 28.
VALUES DATE('2022-02-28') + 1 MONTH ;
00001
----------
2022-03-28
|
In my opinion the easiest solution to this problem is using LAST_DAY. This returns the last day of the month for the date passed to it.
VALUES LAST_DAY(DATE('2022-02-28') + 1 MONTH) ;
00001
----------
2022-03-31
|
Bingo! Just what I wanted.
To really test this I created a table that contained a date column.
CREATE TABLE MYLIB.END_OF_MONTHS (END_OF_MONTH DATE) ; |
I inserted into it all of the end of month dates for 2021.
INSERT INTO END_OF_MONTHS VALUES (DATE('2021-01-31')),
(DATE('2021-02-28')),
(DATE('2021-03-31')),
(DATE('2021-04-30')),
(DATE('2021-05-31')),
(DATE('2021-06-30')),
(DATE('2021-07-31')),
(DATE('2021-08-31')),
(DATE('2021-09-30')),
(DATE('2021-10-31')),
(DATE('2021-11-30')),
(DATE('2021-12-31')),
(DATE('2020-01-31')) ;
|
What about the last row inserted for January 2020. 2020 was the last leap year so this date will show if my approach will work in a leap year as well as a non-leap year.
I can show that the table contains the data I want to use for this test.
SELECT END_OF_MONTH FROM END_OF_MONTHS ; END_OF_MONTH ------------ 2021-01-31 2021-02-28 2021-03-31 2021-04-30 2021-05-31 2021-06-30 2021-07-31 2021-08-31 2021-09-30 2021-10-31 2021-11-30 2021-12-31 2020-01-31 |
This is where it gets interesting. I have created a SQL Select statement that will show the original data from the Table, line 1, that date + 1 month, line 2, and finally the last day for the date + 1 month, line 3.
01 SELECT END_OF_MONTH, 02 END_OF_MONTH + 1 MONTH AS "+1 month", 03 LAST_DAY(END_OF_MONTH + 1 MONTH) AS "Last day" 04 FROM END_OF_MONTHS |
The results show that by using LAST_DAY I get the date for the end of the month, including when it is a leap year.
END_OF_MONTH +1 month Last day ------------ ---------- --------- 2021-01-31 2021-02-28 2021-02-28 2021-02-28 2021-03-28 2021-03-31 2021-03-31 2021-04-30 2021-04-30 2021-04-30 2021-05-30 2021-05-31 2021-05-31 2021-06-30 2021-06-30 2021-06-30 2021-07-30 2021-07-31 2021-07-31 2021-08-31 2021-08-31 2021-08-31 2021-09-30 2021-09-30 2021-09-30 2021-10-30 2021-10-31 2021-10-31 2021-11-30 2021-11-30 2021-11-30 2021-12-30 2021-12-31 2021-12-31 2022-01-31 2022-01-31 2020-01-31 2020-02-29 2020-02-29 |
How to translate that into RPG, using embedded SQL of course.
01 **free
02 dcl-s wkDate date inz(d'2022-01-31') ;
03 exec sql SET :wkDate = LAST_DAY(:wkDate + 1 MONTH) ;
04 dsply ('1 = ' + %char(wkDate)) ;
05 exec sql SET :wkDate = LAST_DAY(:wkDate + 1 MONTH) ;
06 dsply ('2 = ' + %char(wkDate)) ;
07 *inlr = *on ;
|
Line 2: I have defined a date variable, and initialized with the date for January 31, 2022.
Line 3: Using the SQL SET operation I am setting the value of the variable wkDate as I showed above using the SQL LAST_DAY.
Line 4: Display the value in wkDate.
DSPLY 1 = 2022-02-28 |
Line 5: SET the value of wkDate incrementing the date by one month, and then calculating the last day of the month.
Line 6: Display the value in wkDate again.
DSPLY 2 = 2022-03-31 |
On both occasions the end of the next month was returned.
Another example how using SQL can make things so much easier than just trying to do it all in RPG.
This article was written for IBM i 7.4, and should work for some earlier releases too.
Simon, very good information and examples. Thanks.for sharing.
ReplyDeleteI took the request to mean for any date (not just end of month date), when adding a 'month', if the date is the last day of a month, adding a month results in the last day of the following month. If the date is not the last day of the month, then adding a month takes you to the same day of the following month (if such a day exists, otherwise the last day of the next month.) So Feb 28, 2020 + 1 month is March 28 (because 2020 is leap year and feb 28 is not last day of month). Jan 30, 2020 + one month is Feb 29, 2020 because Feb does not have 30 days. Jan 29th, 2021 + 1 month is Feb 28th, 2021. That would seem to be a more useful function, perhaps where adding a month always just increments the month name, and just keeps the date if possible, with exception cases for last day of month or when next month has too few days.
ReplyDeleteYou are describing exactly how the + 1 MONTH works.
DeleteYes, if I want to know the date a month from now I can use that.
But in this case I wanted to know the end of next month, regardless of today's date.
As an aside:
ReplyDeleteIf you use the function ADD_MONTHS it will always return a month end if the starting date is a month end, otherwise it will just add the number of monthes
Example:
Select End_Of_Month, Add_Months(End_Of_Month, 1) "Next Month End",
Add_Months(End_Of_Month, -1) "Previous Month End"
from End_Of_Months;
Returns:
END_OF_MONTH Next Month End Previous Month End
2021-01-31 2021-02-28 2020-12-31
2021-02-28 2021-03-31 2021-01-31
2021-03-31 2021-04-30 2021-02-28
2021-04-30 2021-05-31 2021-03-31
2021-05-31 2021-06-30 2021-04-30
2021-06-30 2021-07-31 2021-05-31
2021-07-31 2021-08-31 2021-06-30
2021-08-31 2021-09-30 2021-07-31
2021-09-30 2021-10-31 2021-08-31
2021-10-31 2021-11-30 2021-09-30
2021-11-30 2021-12-31 2021-10-31
2021-12-31 2022-01-31 2021-11-30
2020-01-31 2020-02-29 2019-12-31
Thank you!
ReplyDeleteThanks Simon. Very helpful
ReplyDeleteI use to do it this way:
ReplyDeletewkDate = ((d'2022-01-31' + %days(1) + %months(1)) - %days(1);
This works for every end of month date
I agree you can do it with RPG, but it is harder unless you hard code the end of month date, as you have shown.
DeleteI gave this example of using RPG back in 2013, see here.
dcl-s wkDate date inz(d'2022-12-31') ;
ReplyDeletedcl-s i int(10) ;
for i = 1 to 12;
exec sql SET :wkDate = LAST_DAY(:wkDate + 1 MONTH) ;
dsply ('i = ' + %char(wkDate)) ;
endfor;
*inlr = *on ;