View previous topic :: View next topic |
Author |
Message |
rsantosh Beginner
Joined: 20 Dec 2014 Posts: 38 Topics: 9
|
Posted: Mon Jan 04, 2021 6:50 am Post subject: DAYS vs YEAR return different date |
|
|
Hello,
Subtracting DAYS and YEAR resulting in different date.
What can be the explanation?
Code: |
SELECT DATE('2021-02-28') - 365 DAYS
FROM SYSIBM.SUSDUMMY1
Result : 2020-02-29
SELECT DATE('2021-02-28') - 1 YEAR
FROM SYSIBM.SUSDUMMY1
Result : 2020-02-28
|
Regards,
Santosh |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Nic Clouston Advanced
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
|
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:
Quote: | If a duration of years is added or subtracted, only the year portion of the date is affected. |
Now go and read the full section in the manual to find out what it does when adding/subtracting days. _________________ Utility and Program control cards are NOT, repeat NOT, JCL. |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
rsantosh Beginner
Joined: 20 Dec 2014 Posts: 38 Topics: 9
|
Posted: Mon Jan 04, 2021 8:07 am Post subject: |
|
|
Hi Nic,
Thank you. It helped.
Regards,
Santosh |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
haatvedt Beginner
Joined: 14 Nov 2003 Posts: 66 Topics: 0 Location: St Cloud, Minnesota USA
|
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) |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
Nic Clouston Advanced
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
|
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. |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
rsantosh Beginner
Joined: 20 Dec 2014 Posts: 38 Topics: 9
|
Posted: Tue Jan 05, 2021 2:04 am Post subject: |
|
|
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](images/smiles/icon_smile.gif) |
|
Back to top |
|
![](templates/subSilver/images/spacer.gif) |
|
|