CODEWITHSUNDEEP
sql-servert-sqldatedatetime
beta
beta

Reputation: 5686

TSQL generate months based on start and end date

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?

Upvotes: 0

Views: 1772

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

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

Upvotes: 0

David Shorthose
David Shorthose

Reputation: 4497

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.

Upvotes: 1

Related Questions