List of weeks

Hi,
I would like to have a combobox that is filled with a list of the ws of
the year, starting on a sunday and ending on a saturday, except when the
year doesn't start on a sunday or ends on a saturday, then it should
start/end with the date.
So for the year 2006 it should be:
01-01-2006 to 07-01-2006
08-01-2006 to 14-01-2006
15-01-2006 to 21-01-2006
...
...
17-12-2006 to 23-12-2006
24-12-2006 to 30-12-2006
31-12-2006 to 31-12-2006
Does anyone know how I can do this with SQL?
FrA calendar table might suit your requirement.
http://www.aspfaq.com/show.asp?id=2519
HTH. Ryan
"Frederik Vanderhaeghe" <frederikvanderhaeghe@.gmail.com> wrote in message
news:eTpLHBxfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I would like to have a combobox that is filled with a list of the ws of
> the year, starting on a sunday and ending on a saturday, except when the
> year doesn't start on a sunday or ends on a saturday, then it should
> start/end with the date.
> So for the year 2006 it should be:
> 01-01-2006 to 07-01-2006
> 08-01-2006 to 14-01-2006
> 15-01-2006 to 21-01-2006
> ...
> ...
> 17-12-2006 to 23-12-2006
> 24-12-2006 to 30-12-2006
> 31-12-2006 to 31-12-2006
> Does anyone know how I can do this with SQL?
> Fr
>|||Try using an auxiliary calendar table as mentioned in
http://www.aspfaq.com/show.asp?id=2519
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Frederik Vanderhaeghe" <frederikvanderhaeghe@.gmail.com> wrote in message
news:eTpLHBxfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I would like to have a combobox that is filled with a list of the ws of
> the year, starting on a sunday and ending on a saturday, except when the
> year doesn't start on a sunday or ends on a saturday, then it should
> start/end with the date.
> So for the year 2006 it should be:
> 01-01-2006 to 07-01-2006
> 08-01-2006 to 14-01-2006
> 15-01-2006 to 21-01-2006
> ...
> ...
> 17-12-2006 to 23-12-2006
> 24-12-2006 to 30-12-2006
> 31-12-2006 to 31-12-2006
> Does anyone know how I can do this with SQL?
> Fr
>|||If you are using SQL 2005, you can do it using this technique...
Declare @.StartDate DateTime
Declare @.EndDate DateTime
Set @.StartDate = '01/01/2006' --Date of the sunday to show in the combo box.
Set @.EndDate = '01/01/2007'; --Date used to as the stop point for the combo
box. Does NOT get shown
With DateList( WCommencing ) as (
Select
@.StartDate WCommencing
Union All
Select
DateAdd( w, 7, WCommencing )
From DateList
Where DateAdd( w, 7, WCommencing ) < @.EndDate
)
Select
Convert( VarChar(10), WCommencing, 101 )
+ ' to ' +
Convert( VarChar(10), DateAdd( d, 6, WCommencing ), 101 ) W
From DateList dl
Regards
Colin Dawson
www.cjdawson.com
"Frederik Vanderhaeghe" <frederikvanderhaeghe@.gmail.com> wrote in message
news:eTpLHBxfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I would like to have a combobox that is filled with a list of the ws of
> the year, starting on a sunday and ending on a saturday, except when the
> year doesn't start on a sunday or ends on a saturday, then it should
> start/end with the date.
> So for the year 2006 it should be:
> 01-01-2006 to 07-01-2006
> 08-01-2006 to 14-01-2006
> 15-01-2006 to 21-01-2006
> ...
> ...
> 17-12-2006 to 23-12-2006
> 24-12-2006 to 30-12-2006
> 31-12-2006 to 31-12-2006
> Does anyone know how I can do this with SQL?
> Fr
>