[dba-SQLServer] Series of Sundays in a query

You could use a table variable and a loop like this: 

DECLARE @WeekBeginning datetime
DECLARE @i int
DECLARE @t table(WeekBeginning char(10))
SET @i=1
WHILE @i < 91
BEGIN
	SET @i = @i + 7
	INSERT @t VALUES (CONVERT(char(10),GETDATE() + @i,101))
END
SELECT WeekBeginning FROM @t


HTH
Asger



-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Steve Erbach
Sendt: 9. december 2008 23:57
Til: Discussion concerning MS SQL Server
Emne: [dba-SQLServer] Series of Sundays in a query

Dear Group,

In an ASP.NET application I'm writing for work, I'd like to create a
work scheduling web form. One section contains a check box list of all
employees, another is a radio button list of the different possible
shifts to choose from, and a third is a checkbox list of "week
beginning" dates.  The Supervisor checks off all the employees for a
particular shift, selects the shift, and then selects the weeks that
those shifts apply.

Anyway, I want to display 13 weeks of "week beginning dates like so:

_ 12/14/2008
_ 12/21/2008
_ 12/28/2008
_ 1/4/2009
etc.

I have a table in which each row represents the starting date of each
week for the coming year, but I thought that there might be a SQL
query that I could create to generate 13-weeks' worth of
week-beginning dates, starting with the Sunday immediately coming up.

I searched through Joe Celko's "SQL for Smarties", reading the chapter
on temporal data in SQL, but I didn't see anything suitable.

Any ideas?  The brute force approach is a 13-query UNION something like
this:

SELECT DATEADD(day, 8 - DATEPART(weekday, GETDATE()), GETDATE()) AS
WeekBeginning
UNION
SELECT DATEADD(day, 15 - DATEPART(weekday, GETDATE()), GETDATE()) AS
WeekBeginning
UNION
SELECT DATEADD(day, 22 - DATEPART(weekday, GETDATE()), GETDATE()) AS
WeekBeginning
UNION
etc.

...but this seems TOO brute-forcish.

Regards,

Steve Erbach
Neenah, WI
http://www.TheTownCrank.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com