Generating dates for given date range (SQL Server)

Ever came across situation where you need to find out dates for the given date range. I recently did and came up with below approaches to do the same.

Note: I have used SQL server 2012 to test these code changes.

Assume that you are given a start date as '11-21-2017' and end date as '12-10-2017' and you need to list out (generate) all the dates in between. How would you do that?
You can basically reference any of the tables with number of records which is greater than the date difference of the from date and to date and should be able to run below query.

---

DECLARE @dt1 DATE = '11-21-2017';
DECLARE @dt2 DATE = '12-10-2017';
 
CREATE TABLE #tempTable(id INT,GeneratedDate DATE)
-- relying on sys.columns as I know there would be only 35 dates for the given range
INSERT INTO #tempTable 
SELECT TOP (DATEDIFF(DAY,@dt1,@dt2)-1) 
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) [c],
    NULL
FROM sys.columns 
 
UPDATE #tempTable
SET GeneratedDate=DATEADD(DAY,id,@dt1)
 
SELECT id ,
       GeneratedDate
FROM #tempTable

---

Output:

Another way to do the same is using while loop. You can start from beginning date and keep looping until you reach to the end date.

---

DECLARE @dt1 DATE = '11-21-2017'
DECLARE @dt2 DATE = '12-10-2017'
DECLARE @counter INT=1;
 
CREATE TABLE #tempTable(GeneratedDate DATE)
 
WHILE (@dt1<@dt2)
BEGIN
    SELECT @dt1 = DATEADD(DAY,@counter,@dt1)
    INSERT INTO #tempTable
         ( GeneratedDate )
    VALUES  ( @dt1 
           )
END
SELECT * FROM #tempTable

---

For the first approach, you can rely on tables as long as you have the number of rows more than the date difference but what if you need to test out the date range with difference more than the number of record in that table.

With common table expression variables, you can create rows using (Table Value Constructors) and generate n rows without referencing any table. Simple implementation is as below. It also generates the same list as above.

Try playing around the number of records you can generate with cross joins. In my case, it would generate (9⁵) records, which are good enough to find the dates for a huge range. If you need more, you can keep adding the CTE variable and it would work.

---

DECLARE @dt1 DATE = '11-21-1930';
DECLARE @dt2 DATE = '12-10-2017';
 
WITH myCTE AS 
(
    SELECT MyTable.number 
    FROM 
  (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) MyTable(number)
)
SELECT TOP (DATEDIFF(DAY,@dt1,@dt2)-1) 
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) [Index],
    DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT 1)),@dt1) [Generated Date]
FROM myCTE cte1,myCTE cte2, myCTE cte3, myCTE cte4, myCTE cte5

---

If you have other ways to achieve the same, please feel free to leave the comments/suggestions.