Re: Invalid dates in data transfer

Dennis Lovelady wrote:

<<SNIP>>

So for that, you can use a CASE :

SELECT field1, field2,
CASE WHEN myDate < '1904-01-01'
THEN DATE('1904-01-01')
ELSE myDate
END, Field4, ...

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.

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. If the CASE were to replace all values outside of the supported 100 year window\range, then setting the out of range value to the NULL value would IMO be a better choice. Replacing 1918-10-14 with the NULL value is probably better than replacing that date with the 1940-01-01 because that valid date value is unlikely to be generally recognized intuitively as a low-value; i.e. although whoever coded the CASE might find it obvious, others probably would not.

Regards, Chuck