Tuesday, February 21, 2023

Using SQL to get the first of the month for a date

I have used the LAST_DAY scalar function to return last day of the month many times. I was interested to see in IBM i 7.5 TR1 and IBM i 7.4 TR7 that a new matching scalar function was added, FIRST_DAY.

As the name suggests FIRST_DAY will return the fist day of the month of the date passed to the scalar function. For example:


I have used FIRST_DAY and LAST_DAY to return the first and last days of the month for the current date. Rather than have this information in two columns I have concatenated these together to produce one result. I know the SQL purist are going to criticize me as I have used the double pipe symbols ( || ) rather than CONCAT. They will, rightly, point out that this using the double pipe will not work on other Db2 platforms. As I will only ever run this code in an IBM i partition, I am not concerned by this lack of cross-platform compatibility.

The result from the above statement is:

2023-02-01 thru 2023-02-28

If I were to use this in a more "real life" scenario I would want to do this with a value from a table or file.

In the following example I have a table, TESTFILE, with a column called DUEDATE, which is a date type column. I am going to show the date in DUEDATE, and then use FIRST_DAY and LAST_DAY to show the dates of the start and end of the DUEDATE's month:

02         FIRST_DAY(DUEDATE) AS "First day",
03         LAST_DAY(DUEDATE) AS "Last day"

Line 2: I am using FIRST_DAY, with DUEDATE, to get the date of the start of the month.

Line 3: I am using LAST_DAY to get the date of the end of the month.

The results are:

DUEDATE      First day    Last day
----------   ----------   ----------
2023-02-13   2023-02-01   2023-02-28
2023-03-15   2023-03-01   2023-03-31
2024-02-09   2024-02-01   2024-02-29

The results are all as I expected. The last result, for February 2024, returns '2024-02-29' as 2024 is a Leap Year.

If I want to I could use both of these scalar functions in a RPG program:

01  **free
02  ctl-opt option(*srcstmt) ;

03  dcl-s FirstOfMonth date(*usa) ;
04  dcl-s LastOfMonth like(FirstOfMonth) ;

05  exec sql SET :FirstOfMonth = FIRST_DAY(CURRENT_DATE) ;
06  exec sql SET :LastOfMonth = LAST_DAY(CURRENT_DATE) ;

07  dsply ('First : ' + %char(FirstOfMonth)) ;
08  dsply ('Last  : ' + %char(LastOfMonth)) ;

Line 1: All code written in 2023 should be free.

Line 2: My favorite control option.

Lines 3 and 4: I have defined two date fields. Notice the DATE(*USA), this means that the date contained within will show in USA format.

Line 5: Use the SQL SET statement to get the first day of the current month.

Line 6: Use the SQL SET statement to get the last day of the current month.

Lines 7 and 8: use the RPG display operation code to display the contents of the two date variables.

After compiling, when the program is run I see:

DSPLY  First : 02/01/2023
DSPLY  Last  : 02/28/2023

I know I will be using FIRST_DAY almost as often as I do LAST_DAY.


You can learn more about the FIRST_DAY SQL scalar function from the IBM website here.


This article was written for IBM i 7.5 TR1 and 7.4 TR7.

1 comment:

  1. Thanks, Simon. This'll save so much messing around!
    Tim G.


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.