Get a list of dates between two dates using a function

Dan_Atkinson_31532 0
Viewed 183517

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:

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.

date sql-server
21 Answers
DECLARE @StartDate DATE = '2017-09-13',         @EndDate DATE = '2017-09-16'

SELECT date  FROM (   SELECT DATE = DATEADD(DAY, rn - 1, @StartDate)   FROM    (
    SELECT TOP (DATEDIFF(DAY, @StartDate, DATEADD(DAY,1,@EndDate)))
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    ORDER BY s1.[object_id]   ) AS x ) AS y

Result:

2017-09-13

2017-09-14

2017-09-15

2017-09-16
user_86687360
Related