Re: SQL date math & April 31st

from: Jon Paris <jon.paris@xxxxxxxxxxxxxx>
subject: Re: SQL date math & April 31st

You should be able to rely on the result being a valid date if SQLSTATE is
01506 - whether that is "correct" on not depends on your rules. It appears
to be using the same rule as RPG's date calcs - i.e. roll the day back
until it is valid.

Given that the description for 01506 is "An adjustment was made to a DATE
or TIMESTAMP value to correct an invalid date resulting from an arithmetic
operation." it is not telling you there is an actual error - note
"adjustment".

I'd be inclined to modify my test of SQLSTATE to check if the first two
characters indicate an error and handle it that way. If no data is an error
you'd be testing for > '01' - if not then > '02'.


Jon Paris