How to create a range of dates

Question

I have a table named CN in SQL Server 2005:

ID    Startdate                 Enddate
1417  2015-09-30 09:45:00.000   2015-10-10 09:45:00.000
973   2015-09-14 11:03:00.000   2015-09-24 11:03:00.000

I need a query which give me the following result

 ID         Dates
 1417       2015-09-30
 1417       2015-10-01
 1417       2015-10-02
 1417       2015-10-03
 1417       2015-10-04
 1417       2015-10-05
 1417       2015-10-06
 1417       2015-10-07
 1417       2015-10-08
 1417       2015-10-09
 1417       2015-10-10
 973        2015-09-14
 973        2015-09-15
 973        2015-09-16
 973        2015-09-17
 973        2015-09-18
 973        2015-09-19
 973        2015-09-20
 973        2015-09-21
 973        2015-09-22
 973        2015-09-23
 973        2015-09-24

That is the id and the list of dates from startdate to enddate including startdate and enddate in the result.

Answer 1

You can use a numbers table for this. Here's a very simple way to create one that will support 5 year+ ranges:

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY);

INSERT dbo.Numbers SELECT ROW_NUMBER() OVER (ORDER BY name) 
FROM sys.all_objects;

Now, given the sample data:

CREATE TABLE #CN(ID INT, StartDate SMALLDATETIME, EndDate SMALLDATETIME);

INSERT #CN(ID, StartDate, EndDate)
SELECT           1417,'2015-09-30 09:45','2015-10-10 09:45'
UNION ALL SELECT 973 ,'2015-09-14 11:03','2015-09-24 11:03';

We can join against the numbers table like this in order to produce the result you're looking for:

;WITH cn AS 
(
  SELECT ID,
         s = DATEADD(DAY, 0, DATEDIFF(DAY, '19000101', StartDate)),
         e = DATEADD(DAY, 0, DATEDIFF(DAY, '19000101', EndDate))
  FROM #CN
)
SELECT cn.ID, DATEADD(DAY, n.Number-1, cn.s)
  FROM cn
  INNER JOIN dbo.Numbers AS n
  ON n.Number <= DATEDIFF(DAY, cn.s, cn.e) + 1;

Clean up your #temp table:

DROP TABLE #CN;

Answer 2

As per https://stackoverflow.com/q/1378593/2451726

...

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;

Or:

SELECT * FROM dbo.CN CROSS APPLY dbo.ExplodeDates(CN.startdate, CN.enddate) AS d;

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.