SQL query to return all dates between two date ranges for SQL Server 2005

There is a post ID: 23398111 which shows how to do this in Oracle but I need a solution for SQL Server,
See link @ http://www.experts-exchange.com/Database/Oracle/Q_23398111.html

The only solution I could come up with is be using an existing table that already contains rows see query below:
SELECT CONVERT(char(10),dateadd(day,ROW_NUMBER() OVER (order by ColD),getdate()), 112) CurrDate
FROM Tbl
HelmutPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
The query would be:


declare @dtBegin datetime
     ,@dtEnd datetime
set @dtbegin = convert(datetime, '01/01/2008',101)
set @dtEnd = convert(datetime, '11/01/2008',101)

;with DateRange as
(
select @dtBegin theDAte
union all
select dateadd(d,n,@dtBegin) from vw_nums where n <= datediff(d,@dtBegin,@dtEnd)
)
select * from DateRange


and it uses the view below.
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
go

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Louis01Commented:
This is a function I use for exactly that purpose:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnCalendar]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnCalendar]
GO
 
CREATE FUNCTION [dbo].[fnCalendar](@StartDate [smalldatetime], @EndDate [smalldatetime])
RETURNS @retMonth TABLE (
	[CalDate] [smalldatetime] NULL
) WITH EXECUTE AS CALLER
AS 
BEGIN
    IF @StartDate IS NOT NULL
      AND @EndDate IS NOT NULL 
      BEGIN
		DECLARE @Duration int
		SET @Duration = (SELECT DATEDIFF(day, @StartDate, @EndDate))
        SET @StartDate = CONVERT(DATETIME, CONVERT(CHAR(10), DATEADD(d, (DATEPART(d, @StartDate) * -1) + 1, @StartDate), 120))
 
        DECLARE @Counter INTEGER
        SET @Counter = 0
        WHILE @Counter < @Duration
          BEGIN
            INSERT INTO
              @retmonth
            VALUES
              (
               DATEADD(DAY, @Counter, @StartDate))
            SET @Counter = @Counter + 1
          END
      END  
    RETURN
   END
GO
 
select * from dbo.fnCalendar('01-Jan-2007', '11-Nov-2008')

Open in new window

0
BrandonGalderisiCommented:
I think that in the interest of "first come first serve" and considering I provided a solution almost a full month sooner, that I should get the full answer.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Louis01Commented:
I posted a different solution because the one proposed by BrandonGalderisi was still running 20 minutes after I tried it. (It subsequently gave an Exception of type 'System.OutOfMemoryException'). In the interest of not splitting hairs though, I am happy if BrandonGalderisi has the 50 points.
0
BrandonGalderisiCommented:
20 minutes?
0
BrandonGalderisiCommented:
The query I have posted above, which returns 306 records for Jan-1 to Nov-1 runs in 0 ms for me.  That means NO MEASURABLE TIME.

declare @dt datetime
set @dt=getdate()
declare @dtBegin datetime
     ,@dtEnd datetime
set @dtbegin = convert(datetime, '01/01/2006',101)
set @dtEnd = convert(datetime, '11/01/2008',101)
 
;with DateRange as
(
select @dtBegin theDAte
union all
select dateadd(d,n,@dtBegin) from vw_nums where n <= datediff(d,@dtBegin,@dtEnd)
)
select * from DateRange
 
select datediff(ms,@dt,getdate())

Open in new window

0
BrandonGalderisiCommented:
cleanup?  as in delete?
0
Daniel WilsonCommented:
As in, 21 days from now I'm to make a recommendation again.
0
BrandonGalderisiCommented:
I will re-state my earlier comment, but not object.


I think that in the interest of "first come first serve" and considering I provided a solution almost a full month sooner, that I should get the full answer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.