Friday, August 9, 2013

Calculating end of month part II

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_DATE 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_DATE 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.


To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.