How to add 1 month to 30th or 31st Jan and after Feb it should take 30th or 31st Mar respectively

Question

I was facing this problem and spend a lot of time today. So, i thought to share it here:

I have a table where we store debitDate and we have a stored procedure where every month we set the debit date to next month in the table.

So, if its debit date is 29th Jan, 2020 -> 29th Feb, 2020 -> 29th March, 2020 - so it should go on like this. I am using DATEADD() function in the stored procedure.

But for 30th & 31st i am facing issue. It should work like below in upcoming years:

Desired Behaviour:

30th Jan, 2020 -> 29th Feb, 2020 -> 30th Mar, 2020 -> 30th Apr, 2020

30th Jan, 2021 -> 28th Feb, 2021 -> 30th Mar, 2021 -> 30th Apr, 2021

31st Jan, 2020 -> 29th Feb, 2020 -> 31st Mar, 2020 -> 30th Apr, 2020

Issue:

30th Jan, 2020 -> 29th Feb, 2020 -> 29th Mar, 2020 -> 29th Apr, 2020

30th Jan, 2021 -> 28th Feb, 2021 -> 28th Mar, 2021 -> 28th Apr, 2021

31st Jan, 2020 -> 29th Feb, 2020 -> 29th Mar, 2020 -> 29th Apr, 2020

Answer 1

Solution 1:

For solution i have thought i can add a new column to the table as previousDebitDate and when we update the debit date we will check, if previousDebitDate day is 30 or 31. If true then

DATEADD(MONTH, 2, @previousDebitDate)

else

DATEADD(MONTH, 1, @debitDate)

If anyone has a better solution please feel free to post your answer.

Solution 2:

For this issue a better solution is to add debitDay as a new column to the table and save only day part (ex: 30) and calculate each month debit date on the fly.

I think Solution 2 is better! Thanks @Arvo!!!

Answer 2

Maybe I 've understand very well & maybe not, but here's what I think you're looking for

CREATE TABLE Data
(
  Dates DATE
);

INSERT Data(Dates) VALUES
('2020-01-30');
WITH CTE AS
(
  SELECT Dates, 
         DATEADD(Month, 1, Dates) NextMonth, 
         DAY(EOMONTH(DATEADD(Month, 1, Dates))) LastDay
  FROM Data
  UNION ALL
  SELECT DATEADD(Month, 1, Dates),
         DATEADD(Month, 1, NextMonth),
         DAY(EOMONTH(DATEADD(Month, 1, NextMonth)))
  FROM CTE
  WHERE Dates <= '2021-12-31'
)
SELECT Dates, NextMonth, DATEFROMPARTS(YEAR(Dates), MONTH(NextMonth),
              CASE WHEN LastDay > 30 THEN 30 ELSE LastDay END) Value
FROM CTE;

Which 'll returns:

+------------+------------+------------+
|   Dates    | NextMonth  |   Value    |
+------------+------------+------------+
| 2020-01-30 | 2020-02-29 | 2020-02-29 |
| 2020-02-29 | 2020-03-29 | 2020-03-30 |
| 2020-03-29 | 2020-04-29 | 2020-04-30 |
| 2020-04-29 | 2020-05-29 | 2020-05-30 |
| 2020-05-29 | 2020-06-29 | 2020-06-30 |
| 2020-06-29 | 2020-07-29 | 2020-07-30 |
| 2020-07-29 | 2020-08-29 | 2020-08-30 |
| 2020-08-29 | 2020-09-29 | 2020-09-30 |
| 2020-09-29 | 2020-10-29 | 2020-10-30 |
| 2020-10-29 | 2020-11-29 | 2020-11-30 |
| 2020-11-29 | 2020-12-29 | 2020-12-30 |
| 2020-12-29 | 2021-01-29 | 2020-01-30 |
| 2021-01-29 | 2021-02-28 | 2021-02-28 |
| 2021-02-28 | 2021-03-28 | 2021-03-30 |
| 2021-03-28 | 2021-04-28 | 2021-04-30 |
| 2021-04-28 | 2021-05-28 | 2021-05-30 |
| 2021-05-28 | 2021-06-28 | 2021-06-30 |
| 2021-06-28 | 2021-07-28 | 2021-07-30 |
| 2021-07-28 | 2021-08-28 | 2021-08-30 |
| 2021-08-28 | 2021-09-28 | 2021-09-30 |
| 2021-09-28 | 2021-10-28 | 2021-10-30 |
| 2021-10-28 | 2021-11-28 | 2021-11-30 |
| 2021-11-28 | 2021-12-28 | 2021-12-30 |
| 2021-12-28 | 2022-01-28 | 2021-01-30 |
| 2022-01-28 | 2022-02-28 | 2022-02-28 |
+------------+------------+------------+

Much better

WITH CTE AS
(
  SELECT 1 N, Dates, Dates ExpectedValue
  FROM Data
  UNION ALL
  SELECT N+1, DATEADD(Month, 1, Dates), DATEFROMPARTS(YEAR(ExpectedValue), MONTH(DATEADD(Month, 1, ExpectedValue)), 
               CASE WHEN DAY(EOMONTH(DATEADD(Month, 1, ExpectedValue))) > 30 THEN 30
                    ELSE DAY(EOMONTH(DATEADD(Month, 1, ExpectedValue)))
               END)
  FROM CTE
  WHERE N < 15
)
SELECT *
FROM CTE
ORDER BY N;

Returns:

+----+------------+---------------+
| N  |   Dates    | ExpectedValue |
+----+------------+---------------+
|  1 | 2020-01-30 | 2020-01-30    |
|  2 | 2020-02-29 | 2020-02-29    |
|  3 | 2020-03-29 | 2020-03-30    |
|  4 | 2020-04-29 | 2020-04-30    |
|  5 | 2020-05-29 | 2020-05-30    |
|  6 | 2020-06-29 | 2020-06-30    |
|  7 | 2020-07-29 | 2020-07-30    |
|  8 | 2020-08-29 | 2020-08-30    |
|  9 | 2020-09-29 | 2020-09-30    |
| 10 | 2020-10-29 | 2020-10-30    |
| 11 | 2020-11-29 | 2020-11-30    |
| 12 | 2020-12-29 | 2020-12-30    |
| 13 | 2021-01-29 | 2020-01-30    |
| 14 | 2021-02-28 | 2020-02-29    |
| 15 | 2021-03-28 | 2020-03-30    |
+----+------------+---------------+

Here is a db<>fiddle