Pages

Wednesday, February 28, 2024

Calculating the difference between two dates in SQL with the result in days

I have written before about how to calculate the difference between two dates using SQL. The returned result is given in years, months, and days. But there are some occasions I need the result in days. For example I need a list of customers who have not paid in over 90 days.

Let me jump into my first example. Here I used a DDS file to contain a "date" field, like many ERP databases the date is held in a numeric field:

01 A          R TESTFILER
02 A            CUSTNBR       10A
03 A            ENTERDTE       8P 0

To calculate how many days difference there is from the date I wrote this post, I first need to convert the field ENTERDTE from a packed numeric to a "true" date, and then I can perform the calculation using it. I am showing a very simple method of doing this. You might find this will not work in your situation. If any of the numeric fields in the file contain a numeric value that is not a valid date, zero for example, then this example would fail with an error.

01  SELECT CUSTNBR,ENTERDTE,
02         DATE(TIMESTAMP_FORMAT(CHAR(ENTERDTE),'YYYYMMDD')) as "Date",
03         TO_CHAR(CURRENT_DATE - DATE(TIMESTAMP_FORMAT(CHAR(ENTERDTE),
                                                   'YYYYMMDD')),'9G99G99')
04            AS "Diff 1",
05         DAYS(CURRENT_DATE) - DAYS(DATE(TIMESTAMP_FORMAT(CHAR(ENTERDTE),
                                                             'YYYYMMDD')))
06            AS "Diff 2"
07    FROM TESTFILE

Line 1: The first two columns in the results will be the two columns from the file.

Line 2: Here I am converting the value in ENTERDTE to be a "true" date. First I need to convert it to a timestamp, using the TIMESTAMP_FORMAT function. And then use the DATE function to extract the date part from the generated timestamp.

Line 3: Basically I am taking the date calculated on line 2 and subtracting the current date from it. I am using the TO_CHAR function to place commas within the returned number.

Line 4: I am spoiling the surprise of what the results of this statement explaining what this line does. To calculate the difference between the current date and ENTERDTE I need to convert both to the number of days, using the DAYS function. Then I can subtract the two to get the number of days difference.

When I execute this statement the results are:

CUSTNBR  ENTERDTE  Date        Diff 1  Diff 2
-------  --------  ----------  ------  ------
A925     20230613  2023-06-13    7,08     214
A113     20230523  2023-05-23    7,29     235

The column "Date" shows that the numeric versions of the "date" was converted to a "true" date.

In the column "Diff 1" I inserted the comma to separate the number of months from the number of days to makes number easier to understand.

The "Diff 2" column contains the difference in days only.

For my next example I decided to use a SQL DDL Table. The Table contains the same information as the DDS file, the only difference is that the "date" is now a timestamp:

01  CREATE TABLE MYLIB.TESTTABLE
02  (CUSTOMER_CODE CHAR(10) NOT NULL WITH DEFAULT,
03   ENTER_TIMESTAMP TIMESTAMP) ;

I am going to use the following statement to show the data contained within:

01  SELECT CUSTOMER_CODE,ENTER_TIMESTAMP,
02         DAYS(ENTER_TIMESTAMP) AS "Days",
03         DAYS(CURRENT_DATE) - DAYS(ENTER_TIMESTAMP) AS "Diff"
04  FROM TESTTABLE

Line 1: The two columns in the Table.

Line 2: Here I display the number of days for date in the timestamp column. The number of days is a count of days since December 31, 1 BCE.

Line 3: Calculate the difference from today to the date contained within the timestamp.

The results are:

CUSTNBR  ENTER_TIMESTAMP             Days    Diff
-------  --------------------------  ------  ----
A751     2023-11-21 09:13:07.000000  738845    53
A108     2024-01-12 11:08:58.000000  738897     1

Having shown just SQL statements, let me show how this can be used in a RPG program. In the below example I am checking if any of the rows above are older than 30 days:

01  **free
02  dcl-ds Data qualified dim(*auto : 9999) ;
03    Customer char(10) ;
04    Entered timestamp ;
05    DaysLate int(5) ;
06  end-ds ;

07  dcl-s Rows int(5) inz(%elem(Data : *max)) ;

08  exec sql DECLARE C0 CURSOR FOR
09             SELECT CUSTOMER_CODE,ENTER_TIMESTAMP,
10                    DAYS(CURRENT_DATE) - DAYS(ENTER_TIMESTAMP)
11               FROM TESTTABLE
12              WHERE (DAYS(CURRENT_DATE) - DAYS(ENTER_TIMESTAMP))
13                      > 30
14                FOR READ ONLY ;

15  exec sql OPEN C0 ;

16  exec sql FETCH C0 FOR :Rows ROWS INTO :Data ;
17  exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;

18  exec sql CLOSE C0 ;

19  dsply ('Rows fetched = ' + %char(Rows)) ;
20  *inlr = *on ;

Line 1: In 2024 everyone should be writing totally free RPG.

Lines 2 – 6: This is an auto-expanding data structure array. It can contain up to a maximum of 9,999 elements.

Lines 3 – 5: The data structure array contains the following subfields: customer code, timestamp, and the number of days difference between the current date and the timestamp.

Line 7: As I am going to perform a multi row fetch I need to give the number of rows to return from the FETCH statement. The code within the INZ keyword will be the maximum number of elements the array Data can have, which is 9,999.

Lines 8 – 14: The declaration for my cursor.

Lines 9 – 11: Is the same as my previous SQL Select statement.

Line 12 – 13: I have the Where clause only selecting rows where the difference between today and the date in the timestamp is greater than 30 days.

Line 15: Open the cursor.

Line 16: I fetch up to number of rows in the variable Rows from the cursor into the data structure array.

Line 17: I want to know how many rows were fetched. I use the GET DIAGNOSTICS to retrieve that using the ROW_COUNT keyword.

Line 18: Close the cursor.

Line 19: Use the Display operation code, DSPLY, to show how many rows were fetch.

Having compiled the program I also added a debug breakpoint at line 20. When I called the program the following was displayed:

DSPLY  Rows fetched = 1

When the program encountered the debug breakpoint I could display the contents of the first, and only element, of the array:

> EVAL data
DATA.CUSTOMER(1) = 'A751      '
DATA.ENTERED(1) = '2023-11-21-09.13.07.000000'
DATA.DAYSLATE(1) = 53

If I had to report on all accounts that were overdue I would build a SQL View, calculating any additional columns of information that would make using the View simple. For example:

  • Date extracted from the timestamp
  • Days difference between today and the date extracted from the timestamp
  • Is the account 30 days late?
  • Is the account 60 days late?
  • Is the account 90 days late?
  • Is the account 120 days late?

With those in mind my View would look like:

01  CREATE OR REPLACE VIEW MYLIB.TESTVIEW
02  (CUSTOMER_CODE,ENTER_TIMESTAMP,ENTER_DATE,
03   DAYS_DIFFERENCE,LATE_30_DAYS,LATE_60_DAYS,LATE_90_DAYS,LATE_120_DAYS) AS
04  (SELECT CUSTOMER_CODE,ENTER_TIMESTAMP,DATE(ENTER_TIMESTAMP),
05          DAYS(CURRENT_DATE) - DAYS(DATE(ENTER_TIMESTAMP)),
06          CASE WHEN (DAYS(CURRENT_DATE) - DAYS(DATE(ENTER_TIMESTAMP))) > 30 
07            THEN BOOLEAN(TRUE)
08          END,
09          CASE WHEN (DAYS(CURRENT_DATE) - DAYS(DATE(ENTER_TIMESTAMP))) > 60 
10            THEN BOOLEAN(TRUE)
11          END,
12          CASE WHEN (DAYS(CURRENT_DATE) - DAYS(DATE(ENTER_TIMESTAMP))) > 90 
13            THEN BOOLEAN(TRUE)
14          END,
15          CASE WHEN (DAYS(CURRENT_DATE) - DAYS(DATE(ENTER_TIMESTAMP))) > 120 
16            THEN BOOLEAN(TRUE)
17          END
18     FROM TESTTABLE)

Line 1: I always have my Views as CREATE OR REPLACE.

Lines 2 and 3: Names of the columns in the View.

Lines 4 – 18: The SQL statement that builds the View.

Line 4: The first two columns are the columns from the Table. The third is the date extracted from the timestamp.

Line 5: Calculate the difference, in days, between the today and the extracted date.

Lines 6 – 8: I am using a Case statement to check if the difference in days is greater than 30. As I am working in a partition that is IBM i 7.5 if the result of the difference is more than 30 I can return a Boolean value of true.

Lines 9 – 11: Same as above but for 60 days.

Lines 12 – 14: This time for 90 days.

Lines 15 – 17: And finally for 120 days.

If I was working on partition that is running a release that is less than IBM i 7.5 I would need to change lines 7, 10, 13, and 16 as earlier releases do not support the Boolean function:

XX            THEN '1'

I create the View and then use the following statement to return the results from it:

01  SELECT * FROM TESTVIEW

This returns:

CUSTOMER                              ENTER
_CODE     ENTER_TIMESTAMP             _DATE
--------  --------------------------  ----------
A751      2023-11-21 09:13:07.000000  2023-11-21
A108      2024-01-12 11:08:58.000000  2024-01-12
A500      2023-12-21 05:28:22.000000  2023-12-21
A742      2023-11-21 15:15:04.000000  2023-11-21
A017      2023-10-22 15:10:17.000000  2023-10-22
A218      2023-09-22 18:07:04.000000  2023-09-22

DAYS        LATE_    LATE_    LATE_    LATE_
DIFFERENCE  30_DAYS  60_DAYS  90_DAYS  120_DAYS
----------  -------  -------  -------- --------
        62  true     true     <NULL>   <NULL>
        10  <NULL>   <NULL>   <NULL>   <NULL>
        32  true     <NULL>   <NULL>   <NULL>
        62  true     true     <NULL>   <NULL>
        92  true     true     true     <NULL>
       122  true     true     true     true

In the late days columns if the entered timestamp fits that columns check I see the word "true". If it does not I see a null value.

If I wanted to get a list of all rows where they are 90 or more late I could just do:

01  SELECT CUSTOMER_CODE,DAYS_DIFFERENCE
02    FROM TESTVIEW 
03   WHERE LATE_90_DAYS = TRUE

As LATE_90_DAYS is a Boolean column I could replace line 3 just with:

03   WHERE LATE_90_DAYS

Either way line 3 is written the same results are returned.

CUSTOMER  DAYS_
_CODE     DIFFERENCE
--------  ----------
A017              92
A218             122

If I want to list all the rows where they are not 90 days late I could just use 'NOT TRUE' in my statement:

01  SELECT CUSTOMER_CODE,DAYS_DIFFERENCE
02    FROM TESTVIEW 
03   WHERE LATE_90_DAYS NOT TRUE

Line 3: This is where I would use the NOT TRUE.

The results are:

CUSTOMER  DAYS_
_CODE     DIFFERENCE
--------  ----------
A751              62
A108              10
A500              32
A742              62

Rather than use the NOT TRUE I could check for null instead.

03   WHERE LATE_90_DAYS IS NULL

I know I have answered more than the original premise. I hope you find the other examples useful as it gives you ideas of how you could use the difference between two dates.

 

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

4 comments:

  1. There is a timestamp diffrence (Timestampdiff) function, I am using here to calculate diffrence in minutes, but it can be used for Days

    SQL to examine INQUIRY messages QSYSOPR messages and response times.
    SELECT TO_CHAR(A.Msg_Time, 'DD/MM/YY HH24:MI') AS "Msg Time",
    To_Char(B.MSG_TIME, 'DD/MM/YY HH24:MI') AS "Rpl Time",
    A.FROM_USER,
    TimestampDiff(4,CHAR(b.Msg_time - a.Msg_Time)) AS "Diff Min",
    A.MSGID,
    CAST(A.MSG_TEXT AS CHAR(60) CCSID 37) AS MSG_TEXT,
    B.FROM_USER AS "Reply User",
    A.FROM_JOB,
    CAST(B.MSG_TEXT AS CHAR(5) CCSID 37) AS RPL
    FROM QSYS2.MESSAGE_QUEUE_INFO A
    INNER JOIN QSYS2.MESSAGE_QUEUE_INFO B
    ON A.MSG_KEY = B.ASSOC_KEY
    WHERE A.MSG_TYPE = 'INQUIRY'
    AND B.MSG_TYPE = 'REPLY'
    AND A.MSGQ_NAME = 'QSYSOPR'
    AND A.MSGID NOT IN ('CPA3394', 'CPA404F', 'CPA405C', 'CPA403D')
    AND B.MSGQ_NAME = 'QSYSOPR'
    ORDER BY B.MSG_TIME DESC

    ReplyDelete
    Replies
    1. I know of TIMESTAMPDIFF, see here.

      The problem with TIMESTAMPDIFF is certain assumptions are made within it:
      - A year is always 365 days, no leap years.
      - All months are 30 days.
      This can be a problem.

      For example:
      VALUES CURRENT_TIMESTAMP - 365 DAYS ;
      = '2023-03-05 11:28:06.584604'

      VALUES DAYS(CURRENT_DATE) - DAYS('2023-03-05') ;
      = 365

      VALUES TIMESTAMPDIFF(16,CHAR(CURRENT_TIMESTAMP - '2023-03-05 11:26:06.344631')) ;
      = 360

      Delete
    2. TIMESTAMDIFF That's unfortunate would not think it would be rocket science for IBM to fix this (Or is it an SQL standard to be wrong?:)

      Delete
    3. As this is a SQL ISO standard I doubt it ever will be as all the companies who provide SQL will need to make the change.

      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.