How do you get the "week start date" and "week end date" from week number in SQL Server?

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, (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Monday,
  DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Sunday