Ken Likes FMTDATE

Published: December 2, 2009

Hey, Ted:

I immediately loaded and tested your FMTDATE function when I saw it in Four Hundred Guru. I just wanted to say "Thank You!" The tool is fantastic! We have mostly ISO and LONGJUL dates in our files and it is always a pain when converting or throwing math at them. No more!

Here is an example of some date math I tried on a LONGJUL order date and an ISO invoice date to calculate "Days-To-Ship" using your function:

SELECT fmtdate(OrderDate, 'LONGJUL', 'ISO-') Ordered,
       fmtdate(InvDate, 'ISO', 'ISO-') Invoiced,
       Date(fmtdate(InvDate, 'ISO', 'ISO-'))-
       Date(fmtdate(OrderDate, 'LONGJUL', 'ISO-'))
  FROM ORDERS

--Ken

I got a good bit of positive response to my Format Date (FMTDATE) function. I've enjoyed using the function in my work, and evidently other readers have, too.

Ken's use of FMTDATE to simplify date arithmetic is good, but it's got a tiny error in it that I want to correct. Subtracting one date from another yields a date duration, which can be misleading. Here's Ken's query over some test data I created.

ORDERED     INVOICED    Numeric Expression
2008-12-31  2009-01-15              15    
2009-01-01  2009-03-31             230    

There are not 230 days between January 1 and March 31. The value 230 means two months and 30 days. I have discussed date durations before.

To make this work, use the DAYS function to create each date to a number that represents the number of days since January 1 of the year 1 A.D.

SELECT fmtdate(OrderDate, 'LONGJUL', 'ISO-') Ordered,   
       fmtdate(InvDate, 'ISO', 'ISO-') Invoiced,        
       days(Date(fmtdate(InvDate, 'ISO', 'ISO-')))-     
       days(Date(fmtdate(OrderDate, 'LONGJUL', 'ISO-')))
  FROM Orders

Here are the results of using the DAYS function.

ORDERED     INVOICED    Numeric Expression
2008-12-31  2009-01-15                15  
2009-01-01  2009-03-31                89  

This makes more sense to me, anyway. I've never thought that it was a good idea to show date durations to end users.

--Ted

Ken had one more question. . .

Hey, Ted:

When invoking FMTDATE from RPG, I am getting an error due to the number of parameters being invalid. I see that it's because the service program expects five parameters to be passed. Correct me if I'm wrong, but I can only assume that SQL is more forgiving when invoking a function with un-sent trailing parameters, because I'm only passing three on the function call.

With this in mind, I tweaked your code (sorry) to specify options(*nopass) for the last two parameters and recreated module, service program, and function. This took care of my RPG invocation of the service program, but I notice that now an SQL visit to the function returns no nulls as it nicely did before. I could have two versions of FMTDATE--one to create the service program for RPG without the last two parms, and one WITH the parms. I could use the latter to create the function and keep the NULL return capability for SQL, and the former for use in RPG programs without having to add the last two parameters. Do you agree?

--Ken

It is possible to invoke FMTDATE from RPG. After all, it's a service program. Here's how it's done.

H option(*srcstmt: *nodebugio)

D FmtDate         pr            10a   varying
D   inDate                       8p 0 const
D   inFromFmt                    8a   varying const
D   inToFmt                      8a   varying const
D   inNullArray                  6a   const
D   ouNull                       5i 0

D ShipDate        s              7p 0
D ShipDateP       s             10a
D ShipDateNull    s              5i 0

 /free
     *inlr = *on;
     ShipDateP = FmtDate(ShipDate: 'cymd': 'mdyy/': *blanks: ShipDateNull);
     return;

But there's no need to go to all that trouble, Ken. I recommend you use the built-in functions that come with RPG instead of FMTDATE.

monitor;
   ShipDateP = %char(%date(ShipDate:*cymd):*usa);
on-error;
   ShipDateNull = -1;  // or whatever else you might want to do
endmon;

I'm glad that Ken and others found FMTDATE helpful.

--Ted

RELATED STORIES

Formatting Dates with SQL

Query and Date Durations

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot