Get multiple months start and end date within a larger date range-CodePudding

So what im struggling with is lets say i input a start date of the 1st of Jan and end date of the 31st of May, what i need to be returned is:

Jan Start Date, Jan end Date
Feb start Date, Feb End date
March start Date, March End date
April start Date, April End date
May start Date, May End date

The purpose of this is to then filter the data in a temp table to return the data that falls in each months range, so for this example the table would return 5 rows

CodePudding user response：

This returns the output you've specified:

declare @start DATE = '2022-01-01'
declare @end DATE = '2022-05-31'

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month, 1, date)
    from months
    where DATEADD(month, 1, date) < @end
)
select     [Start Date] = date,
           [End Date]   = DATEADD(day, -1, DATEADD(month,1, date))
           
from months