Convert date range into date list in SQL Server

In this post “Convert date range in date list”, we will convert a given date of range into list of dates as per the business requirement. Just have a look on mentioned demo table which has three columns CustomerID, StartDate and EndDate. I want to generate the date list for the given date range for each customer. For example if we have a entry as CustomerID – “1”, StartDate – “10-Dec-2012” and EndDate – “19-Mar-2013”, then this should return a date list for CustomerID 1 with 4 rows. First row for 10-Dec-12 to 31-Dec-12, Second row for 01-Jan-13 to 31-Jan-13, Third row for 01-Feb-13 to 28-Feb-13 and fourth row for 01-Mar-13 to 19-Mar-13.

Mind that in first row i have started with the actual start date of the given date range and in the last row i have put the end date as actual end date. But if there is any more row between first row and last row, it has a date range as whole month.

Have a look on this table;

And the required output from this date range is as below;

Lets generate this with demo script

Create a table as below;

CREATE TABLE DateRange
(
CustomerID INT,
StartDate DATE,
EndDate DATE
)

Insert demo values for customer and date range;

INSERT INTO DateRange
VALUES (1, '10-Dec-12', '19-Mar-13'),
(2, '20-Mar-14', '10-Jul-14')

Create another table to hold Serial number so that we can apply a join with this table to generate the date list for given date range. You can also use a demo DateList table to get the desired output. In my demo i am using serial numbers to generate the desired output.

CREATE TABLE TableSerialNumber
(
RowNumber INT
)

Insert serial numbers up to 100 for this demo using sys.columns table. You can also use another way to insert this.

INSERT INTO TableSerialNumber
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM SYS.COLUMNS

Now write a SQL query to extract the output as required. You can also try with some different way using DateList table too. Here i am using a serial number to generate the rows and date list dynamically for the given date range.

SELECT A.CustomerID
,CONVERT(VARCHAR(20), (CASE WHEN B.RowNumber = 1 THEN A.StartDate
ELSE DATEADD(MONTH, B.RowNumber - 1, DATEADD(DD, -(DATEPART(DD, A.StartDate)) + 1, A.StartDate)) END), 106) AS FromDate
,CONVERT(VARCHAR(20), (CASE WHEN (DATEADD(DD, -(DATEPART(DD, A.StartDate)), DATEADD(MONTH, B.RowNumber, A.StartDate))) < A.EndDate THEN
DATEADD(DD, -(DATEPART(DD, A.StartDate)), DATEADD(MONTH, B.RowNumber, A.StartDate)) ELSE A.EndDate END), 106) AS ToDate
FROM DateRange A
INNER JOIN TableSerialNumber B ON B.RowNumber <= (DATEDIFF(MONTH, A.StartDate, A.EndDate) + 1)

Lets discuss the important tricks used in final query

I have joined the DateRange table with SerialNumber table up to the row number “difference of months + 1” as (DATEDIFF(MONTH, A.StartDate, A.EndDate) + 1) i.e. for first row, DATEDIFF function for month interval will give an output as 3 (Dec to Mar). I have added one more to generate 4 rows for Dec, Jan, Feb and Mar too.

We have generated the required number of rows and need to focus on FromDate and ToDate value now. So have a look on this FromDate column statement;

CONVERT(VARCHAR(20), (CASE WHEN B.RowNumber = 1 THEN A.StartDate
ELSE DATEADD(MONTH, B.RowNumber - 1, DATEADD(DD, -(DATEPART(DD, A.StartDate)) + 1, A.StartDate)) END), 106) AS FromDate

In this column, in case of first row, i have taken the StartDate value as it is, otherwise i have put a formula to make it as the first date of the given month.

Finally in case of ToDate, if EndDate value is less than the month end date value, it indicates the last row for the given date range and so i have put a CASE statement to handle it accordingly. If ToDate value is less than the generated month end date value, it will give you the EndDate value, otherwise it will put the month’s last date as ToDate. Have a look on below statement;

CONVERT(VARCHAR(20), (CASE WHEN (DATEADD(DD, -(DATEPART(DD, A.StartDate)), DATEADD(MONTH, B.RowNumber, A.StartDate))) < A.EndDate THEN
DATEADD(DD, -(DATEPART(DD, A.StartDate)), DATEADD(MONTH, B.RowNumber, A.StartDate)) ELSE A.EndDate END), 106) AS ToDate

We can also generate it in some other way too. For example we can achieve this with a demo DateList table too. There can be some other methods too. If you want to share your own way, you are welcome. Kindly put your sample code in comment section and i will be thankful to you for sharing your knowledge.

Thanks for reading my post and please do share this on your social wall and rate this post.