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…

Accepted Answer

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.

Get a list of dates between two dates using a function

Solution

Problem

Related problems