Get the week start date and week end date from week number

Question

I have a query that counts member's wedding dates in the database.

SELECT    SUM(NumberOfBrides) AS [Wedding Count]   , DATEPART( wk, WeddingDate) AS [Week Number]   , DATEPART( year, WeddingDate) AS [Year] FROM  MemberWeddingDates GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate) ORDER BY SUM(NumberOfBrides) DESC 

How do I work out when the start and end of each week represented in the result set?

SELECT   SUM(NumberOfBrides) AS [Wedding Count]   , DATEPART(wk, WeddingDate) AS [Week Number]   , DATEPART(year, WeddingDate) AS [Year]   , ??? AS WeekStart   , ??? AS WeekEnd FROM  MemberWeddingDates GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate) ORDER BY SUM(NumberOfBrides) DESC 

Answer 1

You can find the day of week and do a date add on days to get the start and end dates..

DATEADD(dd, -(DATEPART(dw, WeddingDate)-1), WeddingDate) [WeekStart]  DATEADD(dd, 7-(DATEPART(dw, WeddingDate)), WeddingDate) [WeekEnd] 

You probably also want to look at stripping off the time from the date as well though.

Answer 2

Here is a DATEFIRST agnostic solution:

SET DATEFIRST 4     /* or use any other weird value to test it */ DECLARE @d DATETIME  SET @d = GETDATE()  SELECT   @d ThatDate,   DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Monday,   DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Sunday