Get all month ranges in given date range in SQL

Question

I am trying to implement a below scenario

I have two dates like below

declare @start DATE = '2011-05-21'
declare @end DATE = '2011-08-17'

My requirement is to generate a table like below based on these two dates

StartDate        EndDate
-------------------------
2011-05-21       2011-05-31
2011-06-01       2011-06-30
2011-07-01       2011-07-31
2011-08-01       2011-08-17
--------------------

I tried the below CTE which is not giving me the output as I wish.

;with months (date1,date2)
AS
(
    SELECT @start,eomonth(@start)
    UNION ALL
    SELECT DATEADD(month,1,date1),eomonth(DATEADD(month,1,date1))
    from months
    where DATEADD(month,1,date2)<=@end
)
select * from months

Its producing these data

2011-05-21  2011-05-31
2011-06-21  2011-06-30
2011-07-21  2011-07-31

So could you please help me to tweak it

Answer 1

You can try to use some conditions in select

• StartDate: If date1 is equal to @start it might choose @start otherwise you can try to use DATEADD(DAY,1,EOMONTH(date1 ,-1)) to get the first day of the month.

• EndDate: If date2 is greater than @end choose @end otherwise get date2.

The script as below

;with months (date1,date2)
AS
(
    SELECT @start,eomonth(@start)
    UNION ALL
    SELECT DATEADD(month,1,date1),eomonth(DATEADD(month,1,date1))
    from months
    where DATEADD(month,1,date1)<=eomonth(@end)
)
select IIF(date1 = @start,@start,DATEADD(DAY,1,EOMONTH(date1 ,-1))) StartDate,
       IIF(date2 > @end,@end,date2) EndDate
from months

sqlfiddle