Looping in SELECT statement in ms sqlserver [duplicate]

Question

Possible Duplicate:
SQL Server 2008 Generate a Series of date times

I have to Loop through a startDate and endDate

The SELECT statement should produce result as..

Expected Output :

------------
Date
------------
09/01/2012 -> startDate
09/02/2012
09/03/2012
.
.
.
.
09/30/2012 -> endDate

i tried

declare @startDate datetime , @endDate endDate
set @startDate='09/01/2012'
set @endDate='09/30/2012'

while DATEDIFF(@startDate,@endDate)!=-1
begin
select @startDate as Date
set @startDate = DATEADD(day,2,@startDate)
end

But its not working out..

it generates 30 outputs..

i want the dates in a single output as in the expected output..

where am i going wrong here guys ?

Answer 1

That will give you a resultset for each loop iteration as you select per iteration.

If you want a single resultset insert into a temp table/variable per iteration then select from it or

;with T(day) as
(
    select @startDate as day
        union all
    select day + 1
        from T
        where day < @endDate
)
select day as [Date] from T

Answer 2

If you want to use a WHILE loop:

declare @startDate datetime , @endDate datetime
set @startDate='09/01/2012'
set @endDate='09/30/2012'

create table #temp (startDate datetime)   

while @startDate <= @endDate
    begin
        insert into #temp
        select @startDate as Date
        set @startDate = DATEADD(day,1,@startDate)
    end

select *
from #temp

drop table #temp

see SQL Fiddle with Demo