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

Alan,

Do you mean that if the argument for DATE() is an invalid date the function
returns NULL? If that were the case, a simple COALESCE would suffice but, if
I'm not mistaken (and, of course, I could be), an invalid argument for DATE
returns an exception, not null.

(BTW, Thanks for IDATE, is has been one of the most useful UDFs in our
shop).


Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Wed, Nov 25, 2009 at 2:41 PM, Alan Campin <alan0307d@xxxxxxxxx> wrote:

A null

On Wed, Nov 25, 2009 at 11:53 AM, 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.

--
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.