Without wanting to be drawn in the debate between SQL vs RPG (both has
their merits and, IMHO, its use depends on your business needs), several
years ago I had a problem similar to yours (I think). We had a table with
several dec(8, 0) fields representing dates and needed to check them for

Although idate (Rob's suggestion, I believe) is a wonderful UDF, we wanted
something leaner and (*maybe*) faster running. The "quick and dirty"
solution I wrote was the following:

DROP Function myLib/ISODATEN ;

Create Function myLib/ISODATEN (Fecha Numeric(8, 0))
Returns Date
Language SQL
Returns null on null input
Not Fenced
Set Option Commit=*None, UsrPrf=*Owner

Declare Date Date ;
Declare StDate Char(10) ;
Declare Exit Handler For SQLException Return Null ;

Set StDate = Char(Fecha) ;
Set Date = DATE(
Substr(STDate, 1, 4) || '-' ||
Substr(STDate, 5, 2) || '-' || Substr(STDate, 7, 2) ) ;
Return Date ;

(Fecha is Spanish for "Date").
The UDF returns either a null value (if the date is invalid) or the Date
value in an ISO format (AAAA-MM-DD)



Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries

Return to Archive home page | Return to MIDRANGE.COM home page