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.

1 comment:

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.