MIDRANGE dot COM Mailing List Archive

Re: can this reject bad data?



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

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