SQL Creating a list of date values based on starting date and ending dates

Question

I have a data set with one observation:

id        Starting date        Ending date    
23         18/8/2013             26/4/2014

How would I be able to create a list of dates? i.e.

id       Date
23       Aug 2013
23       Sep 2013
23       Oct 2013
23       Nov 2013
23       Dec 2013
23       Jan 2014
23       Feb 2014
23       Mar 2014
23       Apr 2014

probably this can help. https://stackoverflow.com/questions/35578721/ibm-db2-generate-list-of-dates-between-two-dates — Juan Carlos Oropeza, Apr 04 '18 at 15:04
Juan Carlos Oropeza is right see my anser in the referenced link - a recursive query will help. You ask abut dates but you show months so you might need to use the month function ... — MichaelTiefenbacher, Apr 04 '18 at 16:31
Possible duplicate of [IBM DB2: Generate list of dates between two dates](https://stackoverflow.com/questions/35578721/ibm-db2-generate-list-of-dates-between-two-dates) — mustaccio, Apr 04 '18 at 16:47

score 1 · Accepted Answer · answered Apr 04 '18 at 15:33

Create a SQL Table-Valued Function as below, this will generate the dates.

CREATE FUNCTION fn_GenerateDates
(
    @StartDate DATETIME, 
    @EndDate DATETIME
)

RETURNS @Output TABLE 
(
      Value NVARCHAR(4000)
)

AS

BEGIN

    INSERT INTO @Output
    SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate)+1) --get only the dates where dates are between the source startdate and enddate
            DATENAME(MONTH, DATEADD(MONTH, number, @StartDate)) + ' ' + CONVERT(VARCHAR(10), YEAR(DATEADD(MONTH, number, @StartDate))) AS Months
        FROM [master].dbo.spt_values 
        WHERE [type] = N'P' 
        ORDER BY number

    RETURN
END

Then your SELECT statement

SELECT tn.id,
    dates.Value 
FROM TableName tn
CROSS APPLY dbo.fn_GenerateDates(tn.StartDate, tn.EndDate) AS dates
ORDER BY tn.id

score 0 · Answer 2 · answered May 10 '18 at 06:31

This link uses DB2 as reference, but same concept can be used for SQL Server with little modifications.

This Approach is Similar to the answer provided by @mvisser

Using Sys.columns to generate the dates

Create A SQL Function to Generate the Dates

CREATE FUNCTION fn_GenerateDates
(
    @StartDate DATETIME, 
    @EndDate DATETIME
)

RETURNS @Outputdates TABLE 
(
      Dates NVARCHAR(4000)
)

AS

BEGIN

INSERT INTO @Outputdates
-- Uses Sys.columns Table to Add Months to Given Startdate -----
SELECT DATENAME(MONTH,gen_date) + ' ' + CONVERT(VARCHAR(10),YEAR(gen_date)) FROM 
        (SELECT DATEADD(month,(ROW_NUMBER() OVER(ORDER BY name) - 1),@StartDate) gen_date FROM SYS.COLUMNS) a
    WHERE gen_date between @StartDate and @EndDate
RETURN
END

Then use Select statement

SELECT t.id,gen_dates.Dates
FROM TableName t
CROSS APPLY dbo.fn_GenerateDates(t.StartDate, t.EndDate) AS gen_dates
ORDER BY t.id

SQL Creating a list of date values based on starting date and ending dates

2 Answers2