Ken Likes FMTDATE
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
|
