Re: can this reject bad data?

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