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

On Jul 23, 2021, at 5:27 PM, Justin Taylor <jtaylor.0ab@xxxxxxxxx> wrote:

I have some date calculations in embedded SQL within an RPG program. It
boils down to this:
values(cast('2021-07-31' as date) - 3 months)

That would give April 31st, which doesn't exist. The value returned is
'2021-04-30', with a SQLSTATE of 01506. Is there a clean way to handle
this? If not, can I rely on the date value to be correct so I can simply
ignore 01506?


TIA
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com