Get a list of dates between two dates using a func

Question

My question is similar to this MySQL question, but intended for SQL Server:

Is there a function or a query that will return a list of days between two dates? For example, lets say there is a function called ExplodeDates:

SELECT ExplodeDates('2010-01-01', '2010-01-13');

This would return a single column table with the values:

2010-01-01
2010-01-02
2010-01-03
2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
2010-01-11
2010-01-12
2010-01-13

I'm thinking that a calendar/numbers table might be able to help me here.

---

Update

I decided to have a look at the three code answers provided, and the results of the execution - as a % of the total batch - are:

• Rob Farley's answer : 18%
• StingyJack's answer : 41%
• KM's answer : 41%

Lower is better

I have accepted Rob Farley's answer, as it was the fastest, even though numbers table solutions (used by both KM and StingyJack in their answers) are something of a favourite of mine. Rob Farley's was two-thirds faster.

Update 2

Alivia's answer is much more succinct. I have changed the accepted answer.

Answer 1

All you have to do is just change the hard coded value in the code provided below

DECLARE @firstDate datetime
    DECLARE @secondDate datetime
    DECLARE @totalDays  INT
    SELECT @firstDate = getDate() - 30
    SELECT @secondDate = getDate()

    DECLARE @index INT
    SELECT @index = 0
    SELECT @totalDays = datediff(day, @firstDate, @secondDate)

    CREATE TABLE #temp
    (
         ID INT NOT NULL IDENTITY(1,1)
        ,CommonDate DATETIME NULL
    )

    WHILE @index < @totalDays
        BEGIN

            INSERT INTO #temp (CommonDate) VALUES  (DATEADD(Day, @index, @firstDate))   
            SELECT @index = @index + 1
        END

    SELECT CONVERT(VARCHAR(10), CommonDate, 102) as [Date Between] FROM #temp

    DROP TABLE #temp

Answer 2

Declare @date1 date = '2016-01-01'
              ,@date2 date = '2016-03-31'
              ,@date_index date
Declare @calender table (D date)
SET @date_index = @date1
WHILE @date_index<=@date2
BEGIN
INSERT INTO @calender
SELECT @date_index

SET @date_index = dateadd(day,1,@date_index)

IF @date_index>@date2
Break
ELSE
Continue
END

Answer 3

WITH TEMP (DIA, SIGUIENTE_DIA ) AS
           (SELECT 
               1, 
               CAST(@FECHAINI AS DATE)
            FROM 
               DUAL
           UNION ALL
            SELECT 
               DIA, 
               DATEADD(DAY, DIA, SIGUIENTE_DIA)
            FROM 
               TEMP
            WHERE
               DIA < DATEDIFF(DAY,  @FECHAINI, @FECHAFIN)   
               AND DATEADD(DAY, 1, SIGUIENTE_DIA) <=  CAST(@FECHAFIN AS DATE)
           )
           SELECT 
              SIGUIENTE_DIA AS CALENDARIO 
           FROM
              TEMP
           ORDER BY   
              SIGUIENTE_DIA

The detail is on the table DUAL but if your exchange this table for a dummy table this works.