Easier Overloading of SQL Functions
June 14, 2016 Hey, Ted
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.
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!