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