DECLARE @Start DATETIME SET @Start = '20100101'
DECLARE @End DATETIME SET @End = '20100430'
DECLARE @d DATETIME
DECLARE @Count INT SET @Count = 1

DECLARE @Results TABLE (
	StartDate DATETIME,
	EndDate DATETIME,
	WeekNo INT
)

SET @d = @Start
WHILE DATEPART(dw, @d) != 1
	SET @d = DATEADD(d, 1, @d)

WHILE @Start <= @End
BEGIN
	INSERT INTO @Results
	SELECT @Start, @d, @Count

	SET @Start = DATEADD(d, 1, @d)
	SET @d = DATEADD(wk, 1, @d)
	SET @Count = @Count + 1
END

SELECT * FROM @Results

quote:

---

Originally posted by Idera

declare @tbl as table(stdt datetime,enddt datetime)
insert into @tbl
select  '01-Jan-2010','30-April-2010' 
;with cte
as
(

select stdt,enddt,stdt as stweek from @tbl t1
union all
select t2.stdt,t2.enddt,DATEADD(wk,1,c1.stweek)from @tbl t2
inner join cte c1 on DATEADD(wk,1,c1.stweek)<t2.enddt


) 

select stweek as startweek,DATEADD(wk,1,stweek)as endweek,ROW_NUMBER()over(order by (select 1))as weekno from cte

---

quote:

---

Originally posted by Idera

Well it depends upon your server setting on how you define your startweek day try this select @@DATEFIRST.
You can set your startweek from 1 to 7 with function set datefirst 'Your day number from 1 to 7'

---