I am preparing the next 5 months date according to the value given in the query I wrote.

 DECLARE @StartDate DATETIME = '2022-03-31', @monthadd INT = 5;
 ; WITH dates AS (
 
   SELECT @StartDate [vade]
   UNION ALL
   SELECT DATEADD(MONTH,1,[vade])
   FROM  dates
   WHERE DATEADD(MONTH,1,[vade]) <= DATEADD(MONTH,@monthadd,@StartDate)
 )
 SELECT * 
 FROM  dates 
 OPTION (MAXRECURSION 0)
 GO

However, when the last day of the month is 31, it is necessary to list the last day, which is the nearest day, in the following months. how do i do this?

Actual results

Edit:

This is a maturity plan. If the person makes installments on the 31st of the month, the payment must be made on the last day of each month. If he does it on the 30th, the month should have 30 if it has 30 days, 30 if it has 31 days, and 29 if it has 29 days. If maturity starts on the 20th, it must be the 20th of each month. Imagine you take out a loan on the 30th of the month. If the month is 29 days, they will ask you to pay on the 29th day, and if the month is 31 days, they will ask you to pay on the 30th day. I know it's very confusing and I'm sorry about that.

CodePudding user response：

If I'm understanding correctly, you want to return the same "day" for each month - except when @StartDate is the last day of the month.

One approach would be to determine if the @StartDate is the last day of the month. If it is, use EOMONTH() to return the last day in each of the subsequent months. Otherwise, use DATEADD() to return the specified "day" in each month. This approach should work for any date.

-- Note: Using 12 months for demo only
DECLARE @StartDate DATE = '2022-03-31';
DECLARE @EndDate DATE = DATEADD(MONTH, 12, @StartDate);

; WITH dates AS (

  SELECT @StartDate [vade], IIF(@StartDate = EOMONTH(@StartDate), 1, 0) AS UseEOM
  UNION ALL
  SELECT IIF(UseEOM = 1
             , EOMONTH(DATEADD(MONTH, 1,[vade]))
             , DATEADD(MONTH, 1,[vade])
        ), UseEOM
  FROM  dates
  WHERE [vade] < @EndDate
)
SELECT [vade] 
FROM  dates 
OPTION (MAXRECURSION 0)

Results for 2022-03-31 (Last Day Of Month)

Results for 2022-03-22 (NOT Last Day Of Month)

db<>fiddle here

CodePudding user response：

The DATEADD function already takes into account of the corner cases, like the end of the month, so you don't need to handle it.

In order to have a cleaner code, you can lay down a stored procedure, that creates (or replaces) a dates_list table and then cycles over the number of months to add to the start date.

DELIMITER //

CREATE OR REPLACE PROCEDURE create_dates_list (
    IN start_date DATETIME, 
    IN num_months INT
)
BEGIN
    DECLARE idx INT DEFAULT 0;

    CREATE OR REPLACE TABLE dates_list (
        date DATE
    );

    WHILE idx <> num_months DO
        INSERT INTO tab VALUES(
            DATEADD(@start_date, INTERVAL @idx MONTH)
        );
        SET idx = idx   1;
    END WHILE;
END //

DELIMITER ;

When you need to obtain new dates, you can refresh that table by setting the parameters and calling the stored procedure:

DECLARE @StartDate DATETIME = '2022-03-31', @monthadd INT = 5;

CALL create_dates_list(@StartDate, @monthadd);

You can freely access the table anytime by using the tools that sql empowers you with.

If you don't need the table to exist for further sessions, you can define the table as TEMPORARY. The official documentation on temporary tables is very detailed and comprehensive of examples, check it out to get to know more about it.