MVSFORUMS.com

rsantosh · Beginner Joined: 20 Dec 2014 Posts: 38 Topics: 9

Hello,

Subtracting DAYS and YEAR resulting in different date.

What can be the explanation?

Nic Clouston · Posted: Mon Jan 04, 2021 7:10 am Post subject:

If you had cared to look up how date arithmetic is performed in DB2 you would have found this:

rsantosh · Beginner Joined: 20 Dec 2014 Posts: 38 Topics: 9

Hi Nic,

Thank you. It helped.

Regards,
Santosh

haatvedt · Posted: Mon Jan 04, 2021 2:22 pm Post subject:

Nic,

try the following query as a "leap day" appears to correctly adjust the date correctly.

SELECT DATE('2020-02-29') - 1 YEAR FROM SYSIBM.SYSDUMMY1

result ==> 2019-02-28
_________________
Chuck Haatvedt

email --> clastnameatcharterdotnet

(replace lastname, at, dot with appropriate
characters)

Nic Clouston · Posted: Mon Jan 04, 2021 4:40 pm Post subject:

Chuck, that was the result Santosh got and was querying. My post was to point him in the right direction to get the explanation.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

rsantosh · Beginner Joined: 20 Dec 2014 Posts: 38 Topics: 9

Hi Nic/Chuck,

Yes, after Nic providing the input I got the clarification.

SELECT DATE('2020-02-29') - 1 YEAR FROM SYSIBM.SYSDUMMY1

gives SQLCODE 0 with warning indicating End-of-Month adjustment.

YEAR simply subtracts from the year part of date and keeps the MM and DD same. 2020-02-29 - 1 YEAR results in 2019-02-29. But since DB2 knows 2019 Feb can not have 29 days it does an adjustment and results in 2019-02-28 hence the warning SQLWARN6.

Regards,
Santosh
P.S. I was also wondering why does SPUFI return RC 004(sqlcode 0 and sqlward6 W). Nic's post cleared both the doubts. Smile