Re: Invalid dates in data transfer

Boys, Boys, Boys....

Since I seemed to have caused the confusion, then let me try clear it up.

Since the OP has *LOVAL dates in his file, he needs to be transfering the
data using a date format with four digit 4 year.

Now, his transfer will work UNLESS he happens to be trying to transfer the
data into EXCEL. If he is trying to go into EXCEL, then he should still use
a date format with a 4 digit year, but he'd need to do something with any
dates, ie. *LOVAL of d'0001-01-01', which are less than d'1904-01-01'. The
suggested case statement, which replaced the i's *LOVAL date with Excel's
is reasonable assuming non-loval dates are > d'1904-01-01'. A better option
would probably be to replace the value with NULL which Excel will handle.

SELECT field1, field2,
CASE WHEN myDate '0001-01-01'
THEN cast(NULL as Date),
ELSE myDate
END

Since the i's *LOVAL date is probably being used as the equivilant of a NULL
value anyway...

The above would still throw and error if you had a valid date less than
1904-01-01, but that's probably a good thing.

In other words, forget about the 100 year window for 2 digit dates. Use 4
digit ones. Instead of using '0001-01-01' as a psedu-NULL, use an actual
NULL value.

Charles



On Fri, Jun 19, 2009 at 11:58 AM, CRPence <CRPbottle@xxxxxxxxx> wrote:

Dennis Lovelady wrote:

Chuck, it was you, I believe, who brought up the Excel limitation
(which, again, was the only point I was addressing in the first
place). You mentioned a limitation in Excel that was less than
the standardized 100-year window. If you cannot stick to your
side of the argument, please don't fault others for not doing so.

Charles Wilt mentioned Excel. Besides...

Excel if of no consequence anyhow. I was warning that there
could still be no joy [now would be generally, across all date
value] with the proposed solution, because the DB2 for i [i.e.
nothing to do with Excel] would still issue errors for any values
outside of its 100 year window. The proposed CASE would not prevent
any date overflow nor date underflow for a 36 to 40 year period that
was outside of the 100-year window, thus any data transfer that
included any of those date values would continue to fail with the
SQL0181. So FWiW I started debating that the database had
limitations imposed by the data transfer application for its use of
SQL, and continued with that same tact. :-)

Regards, Chuck
--
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.