[Solved] Stored procedure to find first day of month when an End Date and number of month differences is given in sql server

by


Use a tally table to generate N number of dates

DECLARE @TillDate DATE = '20151031'
DECLARE @NoOfMonthFromStartDate INT = 5

;WITH Tally(N) AS(
    SELECT TOP(@NoOfMonthFromStartDate)
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM sys.columns
)
SELECT
    DATEADD(MONTH, -(N-1), DATEADD(MONTH, DATEDIFF(MONTH, 0, @TillDate), 0))
FROM Tally

If @TillDate is always the end of a month:

;WITH Tally(N) AS(
    SELECT TOP(@NoOfMonthFromStartDate)
        ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM sys.columns
)
SELECT
    DATEADD(MONTH, -N, DATEADD(DAY, 1, @TillDate))
FROM Tally

1

solved Stored procedure to find first day of month when an End Date and number of month differences is given in sql server