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.