sqlsql-serverdateadd

How can I get the last day of periodic months in SQL?


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

vade
2022-03-31 00:00:00.000
2022-04-30 00:00:00.000
2022-05-30 00:00:00.000
2022-06-30 00:00:00.000
2022-07-30 00:00:00.000
2022-08-30 00:00:00.000

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.


Solution

  • Updated 2022-04-01

    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 so, 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.

    One approach is as follows:

    1. If Maturity Date is last day of month, OR Maturity Day of month is > number of days in subsequent month, use EOMONTH() to return the last day of that month
    2. Otherwise, use DATEADD() and DATEFROMPARTS() to generate the next date using the Maturity Day of month

    SQL:

    -- Note: Using 12 months for demo only
    ; WITH dates AS (
      SELECT @StartDate AS MaturityDate
             , IIF(@StartDate = EOMONTH(@StartDate), 1, 0) AS IsEOM
      UNION ALL
      SELECT 
             CASE -- Maturity date is last day of month OR 
                  -- Maturity "day" is > number of days in current month
                  WHEN IsEOM = 1 OR DAY(@StartDate) > DAY( EOMONTH(NextMaturityDate) ) 
                        THEN EOMONTH( DATEADD(MONTH, 1, MaturityDate ))
                  -- Otherwise, maturity "day" is valid for current month
                  ELSE DATEFROMPARTS(
                             Year(NextMaturityDate)
                             , Month(NextMaturityDate)
                             , DAY(@StartDate)
                          )
             END
             , IsEOM
      FROM  ( SELECT MaturityDate
                     , IsEOM
                     , DATEADD(MONTH, 1, MaturityDate) AS NextMaturityDate
              FROM  dates
            ) t
      WHERE MaturityDate < @EndDate
    )
    SELECT MaturityDate AS [vade] 
    FROM   dates 
    OPTION (MAXRECURSION 0)
    

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

    vade
    2022-03-31
    2022-04-30
    2022-05-31
    2022-06-30
    2022-07-31
    2022-08-31
    2022-09-30
    2022-10-31
    2022-11-30
    2022-12-31
    2023-01-31
    2023-02-28
    2023-03-31
    2023-04-30
    2023-05-31
    2023-06-30

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

    vade
    2022-03-30
    2022-04-30
    2022-05-30
    2022-06-30
    2022-07-30
    2022-08-30
    2022-09-30
    2022-10-30
    2022-11-30
    2022-12-30
    2023-01-30
    2023-02-28
    2023-03-30
    2023-04-30
    2023-05-30
    2023-06-30

    db<>fiddle here