TSQL generate months based on start and end date

Question

I have two variables start and end containing date values, e.g. 2018-05-01 and 2019-02-28. I want to create a table that contains each month in between. The resulting table should look as follows.

Month   Year
5       2018
6       2018
7       2018
8       2018
9       2018
10      2018
11      2018
12      2018
01      2019
02      2019

How can this be achieved?

`SELECT DATEADD(MONTH, n, @start) FROM nums WHERE n BETWEEN 1 AND DATEDIFF(MONTH, @start, @end)`, with `nums` a suitable numbers table (some folks prefer a CTE for this; scour SO for questions on how to make one). I may be off by one, season with salt and pepper to taste to unpack the resulting `DATETIME`. — Jeroen Mostert, Aug 22 '18 at 13:25

score 1 · Accepted Answer · answered Aug 22 '18 at 13:31

Here is a table function I use in MS-SQL

CREATE FUNCTION [dbo].[GetSequencedMonthSplit](@StartDate DATETIME, @EndDate DATETIME)
RETURNS @Results TABLE 
(
    ID INT IDENTITY(1,1) 
    , YearValue INT 
    , MonthValue INT 
    , MonthName NVARCHAR(50) 

)
AS

BEGIN 



IF @StartDate IS NULL OR @EndDate IS NULL 
BEGIN 
    /*GET THE CURRENT CALENDAR YEAR*/
    SELECT 
        @StartDate = DATEFROMPARTS(year(getdate()),1,1)
        ,@EndDate = DATEFROMPARTS(year(getdate()),12,31)
END 



WHILE @StartDate < @EndDate 
BEGIN 
     INSERT INTO @Results (YearValue, MonthValue, MonthName) 
     SELECT 
        DATEPART(year, @StartDate)
        , DATEPART(month, @StartDate) 
        , DATENAME(month,@StartDate) 




    SET @StartDate = DATEADD(month, 1, @StartDate) 
END 


    RETURN  


END

then calling something like this:

select 
    MonthValue AS [Month]
  , YearValue  as [Year]
from 
    dbo.GetSequencedMonthSplit(@StartDate,@EndDate)

should give you something you are looking for.

score 0 · Answer 2 · answered Aug 22 '18 at 17:12

Jeroen Mostert's solution in your comments is the best possible way to go. David's solution will get you what you are looking for but his function is a Multi-statement inline table valued function, you want and inline table valued function for the reasons outlined here. Even if though you are only dealing with a small handful of rows, Multi-statement can poison the performance of queries that use call them.

To create an inline table valued function (iTVF) you just need to understand how tally tables work. Doing so will change your career. The iTVF version of what you are looking for looks like this:

CREATE FUNCTION dbo.MonthYearRange (@startdate DATE, @enddate DATE)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH L1   AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) x(N)),
iTally(N) AS
( SELECT 0 UNION ALL
  SELECT TOP (DATEDIFF(MONTH,@startdate,@enddate)) ROW_NUMBER() OVER (ORDER BY (SELECT 1))
  FROM L1 a CROSS JOIN L1 b CROSS JOIN L1 c)
SELECT [Month] = MONTH(d.dt),
       [Year]  = YEAR(d.dt)
FROM iTally i
CROSS APPLY (VALUES (DATEADD(MONTH,i.N,@startdate))) d(dt);

This query:

DECLARE @startdate DATE = '2018-05-01',
        @enddate   DATE = '2019-02-28';

Returns:

Month       Year
----------- -----------
5           2018
6           2018
7           2018
8           2018
9           2018
10          2018
11          2018
12          2018
1           2019
2           2019

TSQL generate months based on start and end date

2 Answers2