MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » May 2014

Re: can this reject bad data?



fixed

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-2014 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