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

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.