Formatting Dates with SQL, Take 2
May 5, 2015 Hey, Ted
I am trying to use your FMTDATE SQL function but the system tells me it can’t find it. I know FMTDATE is there. Can you help?
Note: The code accompanying this article is available for download here.
I’ve received a compliment or two (at least) for the FMTDATE (format date) function I published back in 2009. (Can it really have been so long? How time flies!) I use it heavily, and I’m not the only one. If you’re not familiar with FMTDATE, please read Formatting Dates with SQL before continuing.
To address Robert’s question, the reason the system said that it could not find the function was that Robert was passing a character argument to the first parameter. I wrote the FMTDATE function to accept a numeric value in the first parameter. One might think that the system would respond that the first parameter was of the wrong data type, but not so. Here’s why: SQL allows two or more functions to have the same name as long as the parameter lists differ.
This phenomenon is known as function overloading, and it’s marvelous. When the system must run a function, it looks for a function of the correct name that has a compatible parameter list. “Compatible” means:
1. The number of parameters must equal the number of supplied arguments
2. The system must be able to convert each argument to the data type of the corresponding parameter in the function definition
To help Robert with his problem was a simple matter of overloading the FMTDATE function. In a matter of minutes, I made two changes of significance to my RPG source code.
1. I renamed the FMTDATE subprocedure to FMTDATEN. This was not necessary. I added the N to emphasize that the subprocedure was for numeric data. (I like clarity.)
2. I added a subprocedure called FMTDATEA, where A stands for alphanumeric. FMTDATE converts the character value to numeric and calls FMTDATEN. Here’s that subprocedure in its entirety.
P FmtDateA b export D pi 10a varying D inDate 8a varying const D inFromFmt 8a varying const D inToFmt 8a varying const D inNullArray 6a const D ouNull 5i 0 /free monitor; return FmtDateN (%dec(inDate:8:0): inFromFmt: inToFmt: inNullArray: ouNull); on-error; ouNull = gNullValue; return *blank; endmon; /end-free P e
After creating the module and service program, I created two (yes, two!) FMTDATE functions.
create function xxx/fmtdate (inDate dec(8,0), inFromFmt varchar(8), inToFmt varchar(8)) returns varchar(10) language rpgle parameter style general with nulls deterministic no sql returns null on null input no external action not fenced no final call allow parallel no scratchpad external name 'XXX/FMTDATE(FMTDATEN)' create function xxx/fmtdate (inDate varchar(8), inFromFmt varchar(8), inToFmt varchar(8)) returns varchar(10) language rpgle parameter style general with nulls deterministic no sql returns null on null input no external action not fenced no final call allow parallel no scratchpad external name 'XXX/FMTDATE(FMTDATEA)'
Two functions of the same name run two different subprocedures in the FMTDATE service program. Is that fine, or what?
One word of warning: If you’re using FMTDATE within programs, as I do, you will have to recompile those programs because of the change to the procedure exports in the service program.
Now, if only IBM would allow overloading of subprocedures in RPG! I could do some powerful if-less programming!