Get the week start date and week end date from week number
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.
Here is a DATEFIRST agnostic solution:
SET DATEFIRST 4 /* or use any other weird value to test it */DECLARE @d DATETIMESET @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
you can also use this:
SELECT DATEADD(day, DATEDIFF(day, 0, WeddingDate) /7*7, 0) AS weekstart, DATEADD(day, DATEDIFF(day, 6, WeddingDate-1) /7*7 + 7, 6) AS WeekEnd