Get the week start date and week end date from week number in SQL Server

Question

I have a query that counts the 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

Try using the below code to find the day of the 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 can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL training online.