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:

01 VALUES FIRST_DAY(CURRENT_DATE) || ' thru ' || LAST_DAY(CURRENT_DATE)

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:

00001 -------------------------- 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:

01 SELECT DUEDATE, 02 FIRST_DAY(DUEDATE) AS "First day", 03 LAST_DAY(DUEDATE) AS "Last day" 04 FROM TESTFILE

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.