Get Months Within a Date Range With SQL Query

This article shows how to get the 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 it one by one.

Method 1: using 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

  1. DECLARE @StartDate  DATETIME,  
  2.         @EndDate    DATETIME;  
  3.   
  4. SELECT   @StartDate = '20141130'          
  5.         ,@EndDate   = '20150301';  
  6.   
  7. SELECT   DateName( month , DateAdd( month , monthid , -1 )) Name,monthid from(  
  8. SELECT  Month(DATEADD(MONTH, x.number, @StartDate)) AS MonthId  
  9. FROM    master.dbo.spt_values x  
  10. WHERE   x.type = 'P'          
  11. AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)  
  12. ) A  
Output of above SQL query

Output of SQL query

Method 2: Using Common Table Expression (CTE)

Using recursive CTE we can do the same thing.

SQL Query 1
  1. DECLARE   
  2.       @start DATE = '20120201'  
  3.     , @end DATE = '20120405'  
  4.   
  5. ;WITH cte AS   
  6. (  
  7.     SELECT dt = DATEADD(DAY, -(DAY(@start) - 1), @start)  
  8.     UNION ALL  
  9.     SELECT DATEADD(MONTH, 1, dt)  
  10.     FROM cte  
  11.     WHERE dt < DATEADD(DAY, -(DAY(@end) - 1), @end)  
  12. )  
  13. SELECT DATENAME(MONTH,dt) NameMONTH(dt) as MonthId  
  14. FROM cte  
SQL Query 2

 

  1. DECLARE   
  2.       @start DATE = '20120201'  
  3.     , @end DATE = '20120405'  
  4.   
  5. ;WITH Numbers (Number) AS  
  6. (SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_ID) FROM sys.all_objects)  
  7. SELECT DATENAME(MONTH,DATEADD(MONTH, Number - 1, @start)) Name,MONTH(DATEADD(MONTH, Number - 1, @start)) MonthId  
  8. FROM Numbers  
  9. WHERE Number - 1 <= DATEDIFF(MONTH, @start, @end)  
Output of above SQL query

name

Method 3: Using SQL function
  1. CREATE FUNCTION dbo.GetMonthList (  
  2.   @StartDate DATETIME,  
  3.   @EndDate   DATETIME  
  4. )  
  5. RETURNS @months TABLE (  
  6.   [monthINT,  
  7.   [NameVARCHAR(20)  
  8. )  
  9. AS  
  10. BEGIN  
  11.   
  12.   DECLARE @MonthDiff INT;  
  13.   DECLARE @counter  INT;  
  14.   
  15.   SET @counter = 0;  
  16.   SELECT @MonthDiff = DATEDIFF(mm, @StartDate, @EndDate);  
  17.   
  18.   WHILE @counter <= @MonthDiff  
  19.   BEGIN  
  20.       INSERT @months   
  21.       SELECT Month(DATEADD(mm, @counter, @StartDate)),DATENAME(MONTH,DATEADD(MONTH, @counter - 1, @StartDate));  
  22.   
  23.     SET @counter = @counter + 1;  
  24.   END  
  25.   RETURN;  
  26. END  
Output

Output

Method 4: 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
  1. DECLARE   
  2.       @StartDate DATE = '20120201'  
  3.     , @EndDate DATE = '20120405'  
  4.   
  5. SELECT DATENAME(MONTH, DATEADD(MONTH, A.MonthId - 1, @StartDate)) Name, (A.MonthId + 1) as MonthId  FROM(  
  6. SELECT 1 AS MonthId  
  7. UNION  
  8. SELECT 2  
  9. UNION  
  10. SELECT 3  
  11. UNION  
  12. SELECT 4  
  13. UNION  
  14. SELECT 5  
  15. UNION  
  16. SELECT 6  
  17. UNION  
  18. SELECT 7  
  19. UNION  
  20. SELECT 8  
  21. UNION  
  22. SELECT 9  
  23. UNION  
  24. SELECT 10  
  25. UNION  
  26. SELECT 11  
  27. UNION  
  28. SELECT 12 ) AS A  
  29. WHERE  A.MonthId <= DATEDIFF(MONTH, @StartDate, @EndDate) + 1;  
Output

result

Summary

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