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.
This mailing list archive is Copyright 1997-2025 by midrange.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 on our policy page. If you have questions about this, please contact
[javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.