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:

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.

1 comment:

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

    ReplyDelete

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.