Formatting Dates with SQL, Take 3

Great technique shared! Function overloading is a real boon to SQL programming. Here’s another way that requires less code and is less invasive (i.e., you won’t have to recompile anything.)

Leave your original FMTDATE function alone and add the following to the SQL source:

create function xxx/fmtdate
(inDate varchar(8), inFromFmt varchar(8), inToFmt varchar(8))
returns varchar(10)
language SQL
specific FMTDATEA
deterministic
returns null on null input

begin
 declare DateNum numeric (8,0);
 declare OutDate varchar(10);
 set DateNum = decimal(inDate);
 set OutDate = FmtDate(DateNum, inFromFmt, inToFmt);
 return OutDate;
end

I’ve used this technique to overload many user-defined functions and keep the HLL programming simple.

–Michael Quigley

This is an excellent idea, Michael. I intend to overload functions your way from now on.

Just to make sure everyone understands what’s going on here, I used RPG to define two different subprocedures to carry out two FMTDATE functions. When I added the version that accepts a character argument, I modified the RPG. In the process, I decided to rename the existing FMTDATE subprocedure to FMTDATEN. If I had not renamed that subprocedure, callers would not have had to be recompiled, but I am nit-picky that way.

Michael’s version requires no RPG changes. Instead, he creates the character version of FMTDATE with SQL only.

Michael’s method is far superior to the one I published! I am very grateful to him for sharing it with us!

RELATED STORIES

Formatting Dates with SQL, Take 2

Formatting Dates with SQL