Start and end of each month between two dates

Question

Given two @START_DATE and @END_DATE parameters, I'd like to write a query that generates pairs of (month_start_date, month_end_date) for every month that exists between those two days, including the ones those dates are in.

E.g. If @START_DATE = '2018-01-14' and @END_DATE = '2018-05-04' (yyyy-MM-dd), I'd like the output to be

month_start_date, month_end_date
2018-01-01, 2018-01-31
2018-02-01, 2018-02-28
2018-03-01, 2018-03-31
2018-04-01, 2018-04-30
2018-05-01, 2018-05-31

Create a calendar table containing the start and end of the months/years of interest. — Tim Biegeleisen, Jul 09 '18 at 13:03
Tip: In the future, include a _question_. Posting requirements is not asking a _specific_ programming question. — HABO, Jul 09 '18 at 13:29

score 1 · Answer 1 · answered Jul 09 '18 at 13:03

1

I tend to go for recursive CTEs for this purpose:

with dates as (
      select datefromparts(year(@start_date), month(@start_date), 1) as dte
      union all
      select dateadd(month, 1, dte)
      from dates
      where dateadd(month, 1, dte) <= @end_date
     )
select dte as start_date, eomonth(dte) as end_date
from dates;

This works as-is for up to 100 months. For more than that, you need to use set the max recursion option.

answered Jul 09 '18 at 13:03

Gordon Linoff

1,242,037
58
646
786

1

Gordon, upvoted, but if the OP had a long term/frequent need for this, would a calendar table approach have some merit? – Tim Biegeleisen Jul 09 '18 at 13:11
@TimBiegeleisen . . . That is an interesting question. I'm not sure which would have better performance for getting months -- for every row used in the calendar table, 30.4 other rows would be read. The recursive CTE or a numbers table might be more efficient. – Gordon Linoff Jul 10 '18 at 01:32

Yogesh Sharma · Answer 2 · 2018-07-09T13:37:49.307

1

You need recursive cte :

with t as (
     select dateadd(day, 1, eomonth(@START_DATE, -1)) as start_dt, @END_DATE as end_dt
     union all
     select dateadd(mm, 1, start_dt), end_dt
     from t
     where dateadd(mm, 1, start_dt)  < @END_DATE
)

select start_dt as month_start_date, eomonth(start_dt) as month_end_date
from t
option (maxrecursion 0);

edited Jul 09 '18 at 13:37

answered Jul 09 '18 at 13:05

Yogesh Sharma

49,870
5
26
52

This is almost working beautifully. But does it generate the start date of the month? I suspect it generates the same day of month as in start date. – disasterkid Jul 09 '18 at 13:24
@Disasterkid. . . I didn't noticed that. – Yogesh Sharma Jul 09 '18 at 13:31

score 1 · Answer 3 · answered Jul 09 '18 at 13:09

Following query returns the required result.

declare @StartDate date = '2018-01-14'
      , @EndDate   date = '2018-05-04';
;with Months as (
select top (datediff(month,@StartDate,@EndDate)+1) 
    [month_start_date] = dateadd(month
               , datediff(month, 0, @StartDate) + row_number() over (order by number) -1
               , 0)
    , month_end_date = dateadd(day,-1,dateadd(month
               , datediff(month, 0, @StartDate) + row_number() over (order by number) 
               ,0))
  from master.dbo.spt_values
  order by [month_start_date]
)
select * from Months;

Start and end of each month between two dates

3 Answers3