Get a list of dates between two dates using a function

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

this few lines are the simple answer for this question in sql server.

WITH mycte AS (   SELECT CAST('2011-01-01' AS DATETIME) DateValue   UNION ALL   SELECT  DateValue + 1   FROM    mycte      WHERE   DateValue + 1 < '2021-12-31' )  SELECT  DateValue FROM    mycte OPTION (MAXRECURSION 0) 

Answer 2

Try something like this:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime) returns table as return ( with   N0 as (SELECT 1 as n UNION ALL SELECT 1) ,N1 as (SELECT 1 as n FROM N0 t1, N0 t2) ,N2 as (SELECT 1 as n FROM N1 t1, N1 t2) ,N3 as (SELECT 1 as n FROM N2 t1, N2 t2) ,N4 as (SELECT 1 as n FROM N3 t1, N3 t2) ,N5 as (SELECT 1 as n FROM N4 t1, N4 t2) ,N6 as (SELECT 1 as n FROM N5 t1, N5 t2) ,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6) SELECT DATEADD(day,num-1,@startdate) as thedate FROM nums WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1 ); 

You then use:

SELECT * FROM dbo.ExplodeDates('20090401','20090531') as d; 

Edited (after the acceptance):

Please note... if you already have a sufficiently large nums table then you should use:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime) returns table as return ( SELECT DATEADD(day,num-1,@startdate) as thedate FROM nums WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1 ); 

And you can create such a table using:

CREATE TABLE dbo.nums (num int PRIMARY KEY); INSERT dbo.nums values (1); GO INSERT dbo.nums SELECT num + (SELECT COUNT(*) FROM nums) FROM nums GO 20 

These lines will create a table of numbers containing 1M rows... and far quicker than inserting them one by one.

You should NOT create your ExplodeDates function using a function that involves BEGIN and END, as the Query Optimizer becomes unable to simplify the query at all.