Guru: Easy Date Difference

January 14, 2019 Ted Holt

Hey, Ted:

The dates in our database are stored as seven-digit packed-decimal values in the common CYYMMDD format. In 2018 I wrote an SQL query that reported the number of days between two dates, but it quit calculating properly as soon as it started using 2019 dates. Can you tell me the proper way to find the difference between two dates in days?

–Becki

I don’t know if “the” proper way exists or not, Becki, but I can show you how to do the required calculation. SQL has some handy built-in functions that address your problem.

The DAYS function returns an ordinal number for a date. You can subtract the ordinal number of one date from the ordinal number of another date to retrieve the number of elapsed days.

DAYS requires a date, so you’ll have to convert your seven-digit numbers to the date data type. For that, use the TIMESTAMP_FORMAT function or its alias, TO_DATE.

And since TIMESTAMP_FORMAT requires a character argument, use CHAR to convert a number to character text.

First, let’s get some test data to play with.

declare global temporary table DateDiffs
  (Key   dec(3), DueDate   dec(7), ShipDate dec(7));
  
insert into session.DateDiffs values
( 1 , 1181231 , 1190101),
( 2 , 1190101 , 1190101),
( 3 , 1190101 , 1181231),
( 4 , 1180506 , 1190506),
( 5 , 1190506 , 1180506),
( 6 , 1190201 , 1190301),
( 7 , 1200201 , 1200301);

Here’s the query, using the CHAR, TIMESTAMP_FORMAT and DAYS functions.

select dd.*, 
       days(timestamp_format(char(ShipDate+19000000),'YYYYMMDD')) -
       days(timestamp_format(char(DueDate +19000000),'YYYYMMDD'))  
from session.DateDiffs as dd

Key	Due date	Ship date	Days late
1	1181231	1190101	1
2	1190101	1190101	0
3	1190101	1181231	-1
4	1180506	1190506	365
5	1190506	1180506	-365
6	1190201	1190301	28
7	1200201	1200301	29

Isn’t that great!? I certainly think so!

You can simplify the query slightly by using my FMTDATE function.

select dd.*, 
       days(date(fmtdate(ShipDate,'CYMD', 'ISO-'))) - 
       days(date(fmtdate(DueDate, 'CYMD', 'ISO-'))) 
from session.DateDiffs as dd;

However, if I were in your shoes, I’d take it a step farther. I would write a function to perform the date arithmetic. That would eliminate all those messy nested built-in functions.

Creating a function isn’t difficult. Start by writing an RPG subprocedure that returns the number of days between two dates.

**free
ctl-opt  actgrp(*new);

dcl-s  FromDate      packed(7)  inz(1190101);
dcl-s  ThruDate      packed(7)  inz(1190201);
dcl-s  Days          packed(7);

*inlr = *on;
Days = Days_after (ThruDate: FromDate);
return;

dcl-proc  Days_after;
   dcl-pi Days_after packed(7);
      ThruDate       packed(7)  const;
      FromDate       packed(7)  const;
   end-pi Days_after;

   monitor;
      return %diff( %date(ThruDate: *cymd):
                    %date(FromDate: *cymd): *days);
   on-error;
      // fixme
   endmon;

end-proc;

The DAYS_AFTER subprocedure is all that matters. The main code before that is for testing purposes only. You’ll throw it away later.

Once you’ve determined that the subprocedure yields the proper results, add the parameter list that SQL requires.

**free
ctl-opt  actgrp(*new);

dcl-s  FromDate      packed(7)  inz(1190101);
dcl-s  ThruDate      packed(7)  inz(1190201);
dcl-s  Days          packed(7);

dcl-s ThruDateNull   int      (   5);
dcl-s FromDateNull   int      (   5);
dcl-s ReturnValNull  int      (   5);
dcl-s ReturnState    char     (   5);
dcl-s FunctionName   varchar  ( 517);
dcl-s SpecificName   varchar  ( 128);
dcl-s MessageText    varchar  (1000);

*inlr = *on;
Days_after (ThruDate: FromDate: Days:
            ThruDateNull: FromDateNull: ReturnValNull:
            ReturnState:
            FunctionName: SpecificName: MessageText);
return;

dcl-proc  Days_after;
   dcl-pi Days_after;
      ThruDate       packed   (   7);
      FromDate       packed   (   7);
      ReturnValue    packed   (   7);
      ThruDateNull   int      (   5);
      FromDateNull   int      (   5);
      ReturnValNull  int      (   5);
      ReturnState    char     (   5);
      FunctionName   varchar  ( 517);
      SpecificName   varchar  ( 128);
      MessageText    varchar  (1000);
   end-pi Days_after;

   ReturnState = *zeros;
   clear MessageText;
   monitor;
      ReturnValue = %diff( %date(ThruDate: *cymd):
                           %date(FromDate: *cymd): *days);
   on-error;
      ReturnState = '85555';
      MessageText = 'A decimal value could not be converted to a date.';
   endmon;

end-proc;

The SQL parameter style provides an easy way to set the SQL state if there’s an error. Notice the error-handling code that I’ve added under ON-ERROR. I set the SQL state to some value greater than 02000 and specified the message text.

When the subprocedure works properly with the new parameter list, throw away the main calcs and turn this code into a module.

**free                                                             
ctl-opt   nomain;                                                  
                                                                   
dcl-proc  Days_after     export;                                   
   dcl-pi Days_after;                                              
      ThruDate       packed   (   7);                              
      FromDate       packed   (   7);                              
      ReturnValue    packed   (   7);                              
      ThruDateNull   int      (   5);                              
      FromDateNull   int      (   5);                              
      ReturnValNull  int      (   5);                              
      ReturnState    char     (   5);                              
      FunctionName   varchar  ( 517);                              
      SpecificName   varchar  ( 128);                              
      MessageText    varchar  (1000);                              
   end-pi Days_after;                                              
                                                                   
   ReturnState = *zeros;                                           
   ReturnValNull = *zeros;                                         
   clear MessageText;                                              
   monitor;                                                        
      ReturnValue = %diff( %date(ThruDate: *cymd):                 
                           %date(FromDate: *cymd): *days);         
   on-error;                                                                 
      ReturnState = '85555';                                                 
      MessageText = 'A decimal value could not be converted to a date.';     
   endmon;                                                                   
                                                                             
end-proc;

Create the module and the service program.

CRTRPGMOD MODULE(xxx/DAYS_AFTER)
          SRCFILE(xxx/QRPGLESRC)
          SRCMBR(DAYS_AFTER)
CRTSRVPGM SRVPGM(xxx/DAYS_AFTER) 
          MODULE(xxx/DAYS_AFTER) 
          EXPORT(*ALL)

Create a function that runs the subprocedure in the service program.

create or replace function xxx.days_after
   (ThruDate  dec(7), FromDate dec(7)) 
   returns dec(7)
   language rpgle
   parameter style sql
   deterministic
   returns null on null input
   external name 'XXX/DAYS_AFTER(DAYS_AFTER)'

Now you can use your handy function.

select dd.*, days_after(ShipDate, DueDate) as DaysLate
  from session.DateDiffs as dd

There you have it! If you can write an RPG subprocedure — and you can — you can write an SQL function.

2 thoughts on “Guru: Easy Date Difference”

Jason says:

January 14, 2019 at 2:14 pm

Rather than defining the function as an external SQL function in RPGLE, wouldn’t it instead make more sense to simply define the function in SQL using the code you already defined? I haven’t verified the syntax on this but it should get the idea across.

create function diffDays(ShipDate decimal(8), DueDate decimal(8))
returns decimal(8)
begin
return days(date(fmtdate(ShipDate,’CYMD’, ‘ISO-‘))) – days(date(fmtdate(DueDate, ‘CYMD’, ‘ISO-‘)));
end

Ted Holt says:

July 29, 2021 at 10:30 am

Good idea. This works:

create or replace function diffDays(FromDate decimal(7), ToDate decimal(7))
returns decimal(7)
deterministic
returns null on null input
begin
return dec(days(date(fmtdate(ToDate,’CYMD’, ‘ISO-‘))) –
days(date(fmtdate(FromDate, ‘CYMD’, ‘ISO-‘))), 7, 0);
end;

select d.ShipDate ,d.DueDate,
diffDays (d.DueDate, d.ShipDate) as Diff
from session.DateDiffs as d;

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search