Easier Overloading of SQL Functions

In Formatting Dates with SQL, Take 2, you have shared a great technique. Function overloading is a real boon to SQL programming. Here’s another way to handle the same issue with what I believe is less code and less invasive (meaning you won’t have to recompile programs that use the FMTDATE service program).

Leave your original FMTDATE function alone, but 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 lots of UDFs and keep the HLL programming simple.

–Michael Quigley

Once again I overlooked the obvious. I did more than the necessary amount of work to make FMTDATE accept character arguments, and I’m very grateful to Michael for pointing it out.

I don’t think I’ll make that mistake again. From now on, I expect I’ll create new SQL functions without modifying the ones upon which they’re based. However, in the case of FMTDATE, I’ve decided not to use this technique because another reader gave me an even better idea. Look for take 3 of Formatting Dates with SQL, coming very soon in Four Hundred Guru!

–Ted

RELATED STORIES

Ease the Burden of Overloading

Formatting Dates with SQL, Take 2