months between two dates in sql server with starting and end date of each of them in sql server

Question

i want to get months between two dates with their starting and end dates.Suppose if i enter startdate as "2017-04-01" and enddate as "2017-07-31", i want list of months i.e April,May,June,July with their starting and end date respectively.Kindly suggest me how it can be achieved.

Answer 1

One method is a recursive CTE:

with cte as (
      select dateadd(day, 1 - day(@startdate), @startdate) as som,
             eomonth(@startdate) as eom
      union all
      select dateadd(month, 1, som), eomonth(dateadd(month, 1, som))
      from cte
      where dateadd(month, 1, som) < @enddate
     )
select *
from cte;

If you want the name of the month, then you can use datename(month, som).