After publishing the post Calculating end of month on how I would do it in RPG/free, yesterday, I received a number of messages on how the same can be achieved in SQL.
I want to thank everyone who sent me a message, I really do appreciate the feedback.
Having tried every snippet I was sent I did find one that I appreciate its simplicity.
The source code below, is based upon the examples sent by John Joiner, David Ford, and Robert Rogerson, who suggested the SQL function LAST_DAY:
01 D wkDate S D 02 D Output S 10 03 /free 04 exec sql 05 set :wkDate = last_day(current_date) ; 06 Output = %char(wkDate:*usa/) |
The LAST_DAY function determines the last day of the month for the expression it is given. In this example the expression is the current date, which is the SQL function CURRENT_DATE. I take the value from the LAST_DAY and move it to a date data type field, wkDate, that can be used in the rest of the program.
I have stored this away in my own examples file to use in the future.
You can learn more about the LAST_DAY function on the IBM website here»
This article was written for IBM i 7.1, and it should work with earlier releases too.
Thanks..Simon for sharing
ReplyDeleteHey Simon, old article, still good. :-) Just a remark: last_day is used in your example, in the text is LAST_DATE twice which should be LAST_DAY, too. Nice site, enjoyed it often :-)
ReplyDeleteThank you for bringing that to my attention. I have made the corrections.
Delete