Creating many different dates, days, months, and years in just one SQL statement

The germ for this post came from a program I found that was used in a job I was making changes to. The program made a data structure of information about various dates, you'll see exactly what below, that was passed to various programs. The program was several thousand lines of unnecessarily complicated RPG code. Looking at the results it was passing back to the calling program I knew it could be done a lot simpler using SQL, rather than the complex calculations this program contained.

Never one to turn down a challenge over the weekend I wrote a SQL RPG program that generates the same information in the data structure just using one SQL Select statement. I thought this would be an interesting thing to share due to some of the SQL functions I used, some I have written before others I have not, and you might find useful if you have to do something similar.

When called this program returned a data structure that contained the following:

Description	Size	Type
Today's day of week, upper case	3	Char
Today's date in *MDY format, without separators	6	Char
This month	2	Char
This quarter	1	Char
This year	4	Char
Last month	2	Char
Last month's quarter	1	Char
Last month's year	4	Char
Yesterday's day, upper case	3	Char
Yesterday's date in *MDY format, without separators	6	Char
(Today – 2 days) day, upper case	3	Char
(Today – 2 days) date in *MDY format, no separators	6	Char
(Today – 3 days) day, upper case	3	Char
(Today – 3 days) date in *MDY format, no separators	6	Char
(Today – 4 days) day, upper case	3	Char
(Today – 4 days) date in *MDY format, no separators	6	Char
(Today – 5 days) day, upper case	3	Char
(Today – 5 days) date in *MDY format, no separators	6	Char
(Today – 6 days) day, upper case	3	Char
(Today – 6 days) date in *MDY format, no separators	6	Char
Not used	3	Char

Yes, all of the data structure's subfields are character. Therefore, making sure that the months have leading zero when appropriate, and finding a simple way to remove the date separator characters became the interesting part. But, if it wasn't interesting why would I bother to try to do this?

At this point rather than show the whole program, I think it would be easier to show a piece at a time how I achieved creating columns to match the desired results.

Let me start by showing how it is possible to get the day of week.

01 SELECT DAYNAME(CURRENT_DATE), 02 SUBSTR(UPPER(DAYNAME(CURRENT_DATE)),1,3) 03 FROM SYSIBM.SYSDUMMY1 'Wednesday' 'WED'

Line 1: I have written about DAYNAME before as the way SQL will return the name of the day of the week. Notice in the results that it is mixed case, with only the first letter as upper case.

Line 2: I have enclosed DAYNAME within a UPPER, the Upper function translate the lower case letters returned from the DAYNAME to upper case. Then as I only need the first three characters of the day's name I use SUBSTR, substring, to only return the first three characters.

Next I need for format today's date as *MDY, convert to character, and without separator characters.

01 SELECT CURRENT_DATE, 02 CHAR(CURRENT_DATE), 03 REPLACE(CHAR(CURRENT_DATE),'/','') 04 FROM SYSIBM.SYSDUMMY1 09/25/19 '09/25/19' '092519'

Line 1: The first column will be the current date, as a date.

Line 2: As the name suggests the CHAR function converts the data to character, the separator characters are included.

Line 3: The REPLACE does as expected, it replaces characters in the string with another. In this case I am replacing the date separator character ( / ) with null. The two apostrophe characters are next to each other, with nothing between them, ( '' ) this is used to signify null. By using null to replace the slash the characters to the right are moved over to replace the null. Thus, the date separators are removed.

For the month if the month is less than October then it needs to start with a zero.

01 SELECT MONTH(CURRENT_DATE), 02 CHAR(MONTH(CURRENT_DATE)), 03 RIGHT('0' || MONTH(CURRENT_DATE),2) 04 FROM SYSIBM.SYSDUMMY1 9 '9 ' '09'

Line 1: The MONTH function extracts the month number from a date.

Line 2: When I use the CHAR the leading zero is removed in the character result.

Line 3: The RIGHT function takes the right most given characters and returns those characters in the results. If I take this month when it has been converted to character I have '9', when I concatenate '0' at to the front of the string I have '09'. Now when I take the right most two characters I return '09' in the results. If the month is October when I concatenate '0' on to the character version of the month I have '010', when I return the right two characters I am left with '10'.

Getting the year is a lot simpler than the month as the year does not start with zero.

01 SELECT YEAR(CURRENT_DATE),CHAR(YEAR(CURRENT_DATE)) 02 FROM SYSIBM.SYSDUMMY1 2019 '2019'

Line 1: The YEAR function extracts the four character year from the date.

Line 2: The CHAR function translate the number to a character.

Having shown the individual parts of the SQL statement I will be using, all that is left is for me to show the program. Due to the data structure, that will contain the results, and the SQL statement itself it looks a lot bigger than it actually is. Let me start with the definitions:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-pr Main extpgm('TESTPGM1') ; 04 *n char(80) ; 05 end-pr ; 06 dcl-ds DS1 qualified ; 07 Day char(3) ; 08 Today char(6) ; 09 ThisMonth char(2) ; 10 ThisMonthQuarter char(1) ; 11 ThisMonthYear char(4) ; 12 LastMonth char(2) ; 13 LastMonthQuarter char(1) ; 14 LastMonthYear char(4) ; 15 D_1_Day char(3) ; 16 D_1_Date char(6) ; 17 D_2_Day char(3) ; 18 D_2_Date char(6) ; 19 D_3_Day char(3) ; 20 D_3_Date char(6) ; 21 D_4_Day char(3) ; 22 D_4_Date char(6) ; 23 D_5_Day char(3) ; 24 D_5_Date char(6) ; 25 D_6_Day char(3) ; 26 D_6_Date char(6) ; 27 Filler char(3) ; 28 end-ds ;

Line 1: As it is 2019 my code will be in totally free RPG.

Line 2: The control options say that I will be using a main procedure, and I want the lines numbers in the object to be the same as the source statement numbers.

Lines 3 – 5: As there is a parameter passed to this program this is the equivalent of an entry parameter list for a Main procedure. There is just one parameter of 80 characters, which is the same size as my data structure.

Lines 6 – 28: This is just a data structure, nothing special. I have tried to make the subfield names meaningful so I do not have to explain what they are. As I cannot use a minus sign/hyphen ( - ) in variable names the subfields that are D_9_Day and D_9_Date are really D-9_Day and D-9_date mean (today – 9).

Onto the Main procedure:

29 dcl-proc Main ; 30 dcl-pi *n ; 31 ReturnData char(80) ; 32 end-pi ; 33 exec sql SET OPTION DATFMT = *MDY ; 34 exec sql SELECT 35 SUBSTR(UPPER(DAYNAME(CURRENT_DATE)),1,3), 36 REPLACE(CHAR(CURRENT_DATE),'/',''), 37 RIGHT('0' || MONTH(CURRENT_DATE),2), 38 CHAR(QUARTER(CURRENT_DATE)), 39 CHAR(YEAR(CURRENT_DATE)), 40 RIGHT('0' || MONTH(CURRENT_DATE - 1 MONTH),2), 41 CHAR(QUARTER(CURRENT_DATE - 1 MONTH)), 42 CHAR(YEAR(CURRENT_DATE - 1 MONTH)), 43 SUBSTR(UPPER(DAYNAME(CURRENT_DATE - 1 DAY)),1,3), 44 REPLACE(CHAR(CURRENT_DATE - 1 DAY),'/',''), 45 SUBSTR(UPPER(DAYNAME(CURRENT_DATE - 2 DAYS)),1,3), 46 REPLACE(CHAR(CURRENT_DATE - 2 DAYS),'/',''), 47 SUBSTR(UPPER(DAYNAME(CURRENT_DATE - 3 DAYS)),1,3), 48 REPLACE(CHAR(CURRENT_DATE - 3 DAYS),'/',''), 49 SUBSTR(UPPER(DAYNAME(CURRENT_DATE - 4 DAYS)),1,3), 50 REPLACE(CHAR(CURRENT_DATE - 4 DAYS),'/',''), 51 SUBSTR(UPPER(DAYNAME(CURRENT_DATE - 5 DAYS)),1,3), 52 REPLACE(CHAR(CURRENT_DATE - 5 DAYS),'/',''), 53 SUBSTR(UPPER(DAYNAME(CURRENT_DATE - 6 DAYS)),1,3), 54 REPLACE(CHAR(CURRENT_DATE - 6 DAYS),'/','') 55 INTO :DS1 56 FROM SYSIBM.SYSDUMMY1 ; 57 ReturnData = DS1 ; 58 end-proc ;

Line 33: I use SQL's SET OPTION to change the date from the default, *ISO on this IBM i partition, to *MDY.

Line 34 – 56: This SQL statement does all the "slicing and dicing" of the date into the various ways I want, and then outputs it into the data structure defined above. All of this done at once in this one SQL statement. That's pretty cool.

Line 57: Moving the contents of the data structure into the variable that is defined as the parameter used for passing the results back to the calling program(s).

So what does the data structure look like after the SQL statement is executed?

> EVAL DS1 DS1.DAY = 'WED' DS1.TODAY = '092519' DS1.THISMONTH = '09' DS1.THISMONTHQUARTER = '3' DS1.THISMONTHYEAR = '2019' DS1.LASTMONTH = '08' DS1.LASTMONTHQUARTER = '3' DS1.LASTMONTHYEAR = '2019' DS1.D_1_DAY = 'TUE' DS1.D_1_DATE = '092419' DS1.D_2_DAY = 'MON' DS1.D_2_DATE = '092319' DS1.D_3_DAY = 'SUN' DS1.D_3_DATE = '092219' DS1.D_4_DAY = 'SAT' DS1.D_4_DATE = '092119' DS1.D_5_DAY = 'FRI' DS1.D_5_DATE = '092019' DS1.D_6_DAY = 'THU' DS1.D_6_DATE = '091919' DS1.FILLER = ' '

Don't think that all of these SQL functions can only be used with dates. Some obviously cannot be used with anything else, but the CHAR, REPLACE, and RIGHT you use with other types of data.

 

You can learn more about this from the IBM website:

• REPLACE function
• RIGHT function

 

This article was written for IBM i 7.3, and should work for some earlier releases too.