How to create a range of dates 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.
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 &lt;= DATEDIFF(DAY, cn.s, cn.e) + 1;


Clean up your #temp table:

DROP TABLE #CN;