× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 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.