CODEWITHSUNDEEP
sqldb2
user90831
user90831

Reputation: 171

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

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

Upvotes: 0

Views: 120

Answers (2)

Tanveer Singh Bhatia
Tanveer Singh Bhatia

Reputation: 426

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

Upvotes: 0

mvisser
mvisser

Reputation: 670

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

Upvotes: 1

Related Questions