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

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.

RELATED STORIES AND RESOURCES

Formatting Dates With SQL, Take 3

SQL Functions You Didn’t Know You Had, Part 1

Guru: More Date And Time Conversions Using SQL

Parameter style SQL

DAYS

TIMESTAMP_FORMAT

CHAR