Re: What is result of SQL DATE() scalar function when argument is not a valid date?

Peter,

The '++++' symbols just mean that there is invalid data in a field. They are
not true "plus" signs in a character field.

Check out Alan Campin's IDATE UDF. If the date value is invalid, it returns
NULL.

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Wed, Nov 25, 2009 at 2:23 PM, Peter Dow <petercdow@xxxxxxxxx> wrote:

I'm trying for an SQL statement that will identify invalid dates in a
file that stores dates as 3 separate fields, year, month and day.
Something like

SELECT DCTR#,DBRDTY,DBRDTM,DBRDTD,
case when
date(digits(dbrdty) || '-' ||
digits(dbrdtm) || '-' ||
digits(dbrdtd)) is null
then 'ER' else 'OK' end
FROM QTEMP/T_GPPDCTR

Using the above statement, the invalid dates are displayed as '++', the
valid dates as 'OK'.

If I try

SELECT DCTR#,DBRDTY,DBRDTM,DBRDTD,
case when
date(digits(dbrdty) || '-' ||
digits(dbrdtm) || '-' ||
digits(dbrdtd)) = '++'
then 'ER' else 'OK' end
FROM QTEMP/T_GPPDCTR

I get "Comparison operator = operands not compatible.". The manual is
less than forthcoming about what is returned when the argument is not a
valid date.

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.