Re: Invalid dates in data transfer

Dennis Lovelady wrote:

Of course those literals would both need to be '1940-01-01' to
be in the one hundred year window; oops :-)

However that CASE would not handle a date overflow and could replace many valid dates. Thus actually ensuring the transfer function uses a four-digit-year is still preferable.

No oops necessary, since the solution was presented only as a
means of getting around the limitations of Excel, which by the
way were misstated.
The apparent lower limit for Excel dates is 1/1/1900.

Any value under 1940 is still an underflow; i.e. the value 0001-01-01 is failing with underflow in the scenario by the OP, so too will any value less than 1940-01-01. Thus an attempt to replace only values prior to 1900 will still leave the transfer failing for any dates between 1900-01-01 and 1939-12-31 inclusive.

Replacing only the value '0001-01-01' would ensure that any dates
outside of the valid window would still fail; i.e. continue to
diagnose as an error, anything other than the *LOWVAL, which is
probably better than possibly corrupting the dates by resetting
a valid date to a consistent yet wrong date.

Umm... I think you'll find that the system has already validated any date field (I mean if the DB knows that it's a date), and
that nothing will fall "outside the valid window," whatever that
is.

All of the date values inserted [with mapping] and stored in the database file will indeed be valid dates. There is however, a 100 year window for which a date can not be retrieved from the file [regardless that the datum represents a valid date] when the application has failed to request to use a four-digit-year during that retrieval. The 100 year window for retrieval of date data type values from the database is 1940-01-01 to 2039-12-31. Any value outside of that range, when a two-digit-year representation is in effect, will generate a /date overflow/ or /date underflow/ error. This requirement exists because the database knows there is no guarantee of a /round trip/ of a date value such as 11/11/11 except within any any one-hundred epoch. If the database were to allow one to retrieve 11/11/11 to represent 1911/11/11, then when that same value was inserted, it would then represent 2011/11/11 which means the meaning was _corrupted_ as I had alluded. Thus the error is issued due to ambiguity inherent in two-digit-year formats.

I think you will also find that there are no *LOWVAL dates in
these fields. I believe that if you debug an RPG program that
attempts to set a date to *LOVAL, it will actually be set to
0001-01-01.

I was using *LOWVAL for a date, as a synonym for the date value 0001-01-01.

I was trying to suggest that all date values outside of the supported 100 year window should be /handled/ in any attempt at resolution to the scenario presented by the OP. In replacing only the date value 0001-01-01, that would /handle/ the situation by ensuring all values other than 0001-01-01 would be replace, while ensuring all out-of-range dates would still cause the SQL0181; IMO a much better approach than possibly changing a date like 1911-10-18 into an ambiguously-valid date like 1940-01-01 or 2039-12-31 [or any value betwixt].

Regards, Chuck