How to find dates between two dates in SQL Server 2005

Question

I need to find the dates between two dates in SQL Server.

where 
    startdate = '2015-12-04 00:00:00.000' 
    and enddate = '2015-12-07 00:00:00.000'

Result should be

2015-12-04 00:00:00.000
2015-12-05 00:00:00.000
2015-12-06 00:00:00.000
2015-12-07 00:00:00.000

Edit your question and provide sample data and desired results. It is too vague as written. — Gordon Linoff, Jan 26 '16 at 14:35
Isn't that timestamps, not dates? (Do you expect `2015-12-10 12:00:00.000` to be returned?) — jarlh, Jan 26 '16 at 14:39
@Gordon if that question is that simple. USE the search function or just google it!! — Tom Wellbrock, Jan 26 '16 at 14:40
@jarlh Thank you for your comment i need to find the date between these timestamps — dhS, Jan 26 '16 at 14:42
Usually simplest way is to create a calendar table (one day = one row). You can then use it for other purposes too. — James Z, Jan 26 '16 at 14:43
Similar querstions: http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates and http://stackoverflow.com/questions/2462738/search-between-dates-and-times-in-sql-server-2008 — Stefan Steinegger, Jan 26 '16 at 14:50

score 0 · Answer 1 · answered Jan 26 '16 at 14:41

Try:

DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = '20151204'
SET @Date2 = '20151210'

SELECT DATEADD(DAY,number+1,@Date1) [Date]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1,@Date1) < @Date2

Or:

declare @startDate date;
declare @endDate date;

select @startDate = '20151204';
select @endDate = '20151210';

with interdate as
(
  select dt = dateadd(dd, 1, @startDate)
  where dateadd(dd, 1, @startDate) < @endDate
  union all
  select dateadd(dd, 1, dt)
  from interdate
  where dateadd(dd, 1, dt) < @endDate
)
select *
from interdate

score 0 · Accepted Answer · answered Jan 26 '16 at 15:04

This will work in sqlserver 2005:

DECLARE @startdate datetime 

DECLARE @enddate datetime

SELECT @startdate ='2015-12-04', @enddate='2015-12-07'

;WITH N(N)AS 
(SELECT 1 FROM(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1)M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
SELECT top (datediff(d, @startdate, @enddate) + 1) 
  dateadd(d, N - 1, @startdate)
FROM tally

Result(I removed timepart):

thanks for you wonderful comment ... many thanks it works .. . :) — dhS, Jan 26 '16 at 15:06

How to find dates between two dates in SQL Server 2005

2 Answers2