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.
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:
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