Get Months Within a Date Range With SQL Query

Introduction

In SQL Server there is no direct function or procedure that returns all the months within a date range (all days between two dates). This article provides a workaround to get the months, including the name(s), of the dates in a range of dates.

Problem Statement

Suppose I have two dates and I need to select all the months in the range of dates. How can we do this?

Solution

There are many ways to select all the months within a date range. Here I am explaining them one by one.

By using the undocumented table "dbo.spt_values”

This table resides in the Master Database and it has a sequence number from 0 to 2047 in the column called "number" for type "P". There is no documentation available in MSDN for this table, so here basically we need a number sequence that helps us to get all the months between two dates.

SQL Query

DECLARE @StartDate  DATETIME,  
        @EndDate    DATETIME;  
  
SELECT   @StartDate = '20141130'          
        ,@EndDate   = '20150301';  
  
SELECT   DateName( month , DateAdd( month , monthid , -1 )) Name,monthid from(  
SELECT  Month(DATEADD(MONTH, x.number, @StartDate)) AS MonthId  
FROM    master.dbo.spt_values x  
WHERE   x.type = 'P'          
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)  
) A  

Output

By using Common Table Expression (CTE)

SQL Query

DECLARE   
      @start DATE = '20120201'  
    , @end DATE = '20120405'  
  
;WITH cte AS   
(  
    SELECT dt = DATEADD(DAY, -(DAY(@start) - 1), @start)  
    UNION ALL  
    SELECT DATEADD(MONTH, 1, dt)  
    FROM cte  
    WHERE dt < DATEADD(DAY, -(DAY(@end) - 1), @end)  
)  
SELECT DATENAME(MONTH,dt) Name, MONTH(dt) as MonthId  
FROM cte  

Using recursive CTE we can do the same thing.

SQL Query 

DECLARE   
      @start DATE = '20120201'  
    , @end DATE = '20120405'  
  
;WITH Numbers (Number) AS  
(SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_ID) FROM sys.all_objects)  
SELECT DATENAME(MONTH,DATEADD(MONTH, Number - 1, @start)) Name,MONTH(DATEADD(MONTH, Number - 1, @start)) MonthId  
FROM Numbers  
WHERE Number - 1 <= DATEDIFF(MONTH, @start, @end)  

Output

By using SQL function

SQL Query

CREATE FUNCTION dbo.GetMonthList (  
  @StartDate DATETIME,  
  @EndDate   DATETIME  
)  
RETURNS @months TABLE (  
  [month] INT,  
  [Name] VARCHAR(20)  
)  
AS  
BEGIN  
  
  DECLARE @MonthDiff INT;  
  DECLARE @counter  INT;  
  
  SET @counter = 0;  
  SELECT @MonthDiff = DATEDIFF(mm, @StartDate, @EndDate);  
  
  WHILE @counter <= @MonthDiff  
  BEGIN  
      INSERT @months   
      SELECT Month(DATEADD(mm, @counter, @StartDate)),DATENAME(MONTH,DATEADD(MONTH, @counter - 1, @StartDate));  
  
    SET @counter = @counter + 1;  
  END  
  RETURN;  
END  

Output

Using “Union” Query

This method is the same as method 1. In this method to generate the sequence, I have used a hardcoded union query.

SQL Query

DECLARE   
      @StartDate DATE = '20120201'  
    , @EndDate DATE = '20120405'  
  
SELECT DATENAME(MONTH, DATEADD(MONTH, A.MonthId - 1, @StartDate)) Name, (A.MonthId + 1) as MonthId  FROM(  
SELECT 1 AS MonthId  
UNION  
SELECT 2  
UNION  
SELECT 3  
UNION  
SELECT 4  
UNION  
SELECT 5  
UNION  
SELECT 6  
UNION  
SELECT 7  
UNION  
SELECT 8  
UNION  
SELECT 9  
UNION  
SELECT 10  
UNION  
SELECT 11  
UNION  
SELECT 12 ) AS A  
WHERE  A.MonthId <= DATEDIFF(MONTH, @StartDate, @EndDate) + 1;  

Output

Conclusion

Using the preceding methods we can get all the months within a date range.