Geek asks and answers

Looping in a SELECT statement in ms sqlserver

Possible duplicate:
SQL Server 2008 Create a series of time dates

I need to loop through startDate and endDate

The SELECT statement should return the result as ..

Expected Result:

------------
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 it doesn’t work out ..

It generates 30 outputs.

I need dates in one output, as in the expected output.

where am I going wrong guys?

+5
source share
3 answers

This will give you a set of results for each iteration of the loop, how are you selectfor the iteration.

If you want one result set to insert into the table / temp variable for each iteration, select 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
+10
source

If you want to use a loop WHILE:

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

. SQL Fiddle with Demo

+2

.

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: Cte Alex offers io a much cleaner way to do this, and what's more, he can do it without using loops or cursors.

0
source

More articles:


All Articles