[dba-SQLServer] Series of Sundays in a query

Steve Erbach erbachs at gmail.com
Tue Dec 9 16:56:35 CST 2008


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



More information about the dba-SQLServer mailing list