Stephen,

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
integrity.

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
Deterministic
Not Fenced
Set Option Commit=*None, UsrPrf=*Owner

Begin
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 ;
END
*************

(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)

HTH,

Regards,
Luis

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries




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

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact