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.

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.

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;

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;

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.

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse, Big Data and Cloud Solutions.
I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have good experience in windows and web application development using ASP.Net and C#.

3 thoughts on “Convert date range into date list in SQL Server”

What you’ve created is an rCTE (Recursive CTE) that COUNTS. Those are really bad for both performance and resource usage even for a row count as small as 10. Please see the following article for how bad they are and why. The reason is that they are a form of “Hidden RBAR”.http://www.sqlservercentral.com/articles/T-SQL/74118/