Looping in SELECT statement in ms sqlserver

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 ?

score 10 · Accepted Answer · answered Sep 12 '12 at 15:27

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

score 2 · Answer 2 · answered Sep 12 '12 at 15:31

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

answered Sep 12 '12 at 15:31

Taryn

242,637
56
362
405

thanks and sqlfiddle in nice.. – Dreamer Sep 12 '12 at 18:33

score 0 · Answer 3 · answered Sep 12 '12 at 15:31

You could create a temp table for the values and select from that in the end, after the iteration.

declare @temp table (TheDate date)

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

while DATEDIFF(day, @startDate, @endDate)!=-1
begin
insert into @temp (thedate) values (@startDate)
set @startDate = DATEADD(day,2,@startDate)
end
select * from @temp

edit: The cte Alex suggest is imo a much cleaner way to do it, and more of a sql way to do it, without using loops or cursors.

Looping in SELECT statement in ms sqlserver

3 Answers3

see SQL Fiddle with Demo