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 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

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 of above SQL query



Method 2: Using Common Table Expression (CTE)

Using recursive CTE we can do the same thing.

SQL Query 1

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

SQL Query 2

 

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 of above SQL query



Method 3: Using SQL function

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



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

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



Summary

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