Wednesday, September 25, 2019

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

calculating different dates using sql

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:

 

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

4 comments:

  1. There are at least two other methods for achieving the same.

    If MONTH wouldn't return a long integer, DIGITS() alone would be beautiful.
    But so one has to somehow get rid of the 8 leading zeros that a DIGITS(MONTH(CURRENT_DATE)) returns.

    But all of those problems wouldn't have arisen if these fields in the DS would be zoned numeric (OVERLAY), as the generated EVALs from the SQL precompiler would do the job of shortening.

    But i don't know which one is faster.

    ReplyDelete
  2. I see Americans still use MDY!
    Cool piece of functionality. Will try it out (with YMD obviously!)

    ReplyDelete
  3. 1. Why!!! Why??? using date format *MDY (or *DMY) with a 2 digit year
    portion which always causes problems. Why not explitely setting the date
    format to *ISO(or at least *USA) and building the date information based on
    this string?

    2. With VARCHAR_FORMAT any timestamp can be returned in almost any format,
    depending on the specified format. It can even return 3 character day and
    month names all in Upper case, lower case and even with a capital letter at
    the begin or the week according ISO guide lines.

    Here a few exaples for converting a date or retrieveing date information
    with VARCHAR_FORMAT.

    Values('YYYY-MM-DD',
    VarChar_Format(Current_Timestamp, 'YYYY-MM-DD')),
    ('YY/DDD',
    VarChar_Format(Current_Timestamp, 'YY/DDD')),
    ('DD.MM.YYYY Year/ISOWeek1',
    VarChar_Format(Timestamp_ISO('2015-12-31'), 'DD.MM.YYYY IYYY/IW')),
    ('DD.MM.YYYY Year/ISOWeek2',
    Varchar_Format(Timestamp_ISO('2015-12-27'), 'DD.MM.YYYY IYYY/IW')),
    ('DD.MM.YYYY Year/ISOWeek3',
    Varchar_Format(TimeStamp_ISO('2018-12-31'), 'DD.MM.YYYY IYYY/IW')),
    ('04.01.2010 Year/ISOWeek4',
    Varchar_Format(Timestamp_ISO('2019-1-4'), 'DD.MM.YYYY IYYY/IW')),
    ('Current_Date',
    Varchar_Format(Current_Timestamp, 'DD.MM.YYYY Q W SSSSS'))
    ;

    Values(VarChar_Format(Current_Timestamp, 'Day, DD. Month YYYY')),
    (VarChar_Format(Current_Timestamp, 'DD-MON-YY / hh12:MI:SS AM')),
    (VarChar_Format(Current_Timestamp, 'YYYY/DDD - DY, month IYYY/IW'))
    ;

    Birgitta

    ReplyDelete
    Replies
    1. To answer your number 1: The application where the original program resides is (very) old, and the programmers who work upon it are equally old, and are still RPG3 guys. As such the date is stored and presented in six long format.

      If this was my application every date would be a date type column.

      Delete

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.