SQL to create a list of weeks - Handy for reporting

Declare @StartDate DateTime
Declare @EndDate DateTime

Set @StartDate = '04/29/2007' --Date of the sunday to show in the combo box.
Set @EndDate = '06/01/2008'; --Date used to as the stop point for the combo box. Does NOT get shown

With DateList( WeekCommencing ) as (
SELECT @StartDate WeekCommencing

UNION All

SELECT DateAdd( w, 7, WeekCommencing )

FROM DateList

WHERE DateAdd( w, 7, WeekCommencing ) < @EndDate ) SELECT Convert( VarChar(10), WeekCommencing, 101 ) + ' to ' + Convert( VarChar(10), DateAdd( d, 6, WeekCommencing ), 101 ) Week FROM DateList dl WHERE WeekCommencing <= GetDate()