I think there is a problem in the way .addDates() works when trying to subtract a month from a date.
I’m trying to use this function in a server action API.
I’m passing a date (let’s call it mydate) and want to obtain a new date (let’s call it newdate) that is one month earlier.
The code view in the server action API looks like this:
Set Value newdate = {{$_GET.mydate.dateAdd(‘months’, -1).formatDate(‘yyyy-MM-dd’)}}
If mydate = 2021-01-31, newdate becomes 2020-12-31, as one would expect.
If, however, mydate = 2020-12-31, newdate becomes 2020-12-01 instead of 2020-11-30
If mydate = 2020-12-30, then newdate is set to 2020-11-30
Is my expectation on how to use the dateAdd() wrong or there is a bug in this function?
Here’s a bit more data, that hopefully may help you:
a) The problem is evident in all 31-day months that follow a 30-day month. Subtracting a month from the 31st day of that month results on the first day of the month original month, not on the last day of the previous month.
b) It is as if the method subtracts the total number of days of the previous month to do the calculation. This is also evident if you subtract a month from March 31st. You end on March 3, because it’s subtracting the 28 days of Feb.
c) A similar problem occurs on the client side. The addMonth(-1) behaves similarly.
Needless to say, this could be causing issues with date calculations out there, and since many times these calculations are used for financial purposes, it has the potential of creating money issues.
SELECT ADDDATE('2021-03-31', INTERVAL -1 MONTH) A, ADDDATE('2021-01-31', INTERVAL -1 MONTH) B, ADDDATE('2020-10-31', INTERVAL -1 MONTH) C;
its on MySQL 8.
