Create Week Date Range column based on the service date

Question

I need to create WeekDateRange column based on the ServiceDate column. Each WeekDateRange value should START with Monday date and END with Sunday date associated with ServiceDate in (mm/dd/yyyy - mm/dd/yyyy) format.

SELECT s.ServiceDate
FROM ServiceInfo AS s

ServiceDate
2022-01-03
2022-01-07
2022-01-15
2022-01-26
2022-01-29
2022-02-01
2022-02-04
2022-02-06
2022-02-07

I tried to use below query, however 2022-02-06 ServiceDate assigns to 02/07/2022 - 02/13/2022 week.

SELECT s.ServiceDate,
       CONCAT(CONVERT(VARCHAR, DATEADD(DAY, 2 - DATEPART(WEEKDAY, 
            s.ServiceDate), CAST(s.ServiceDate AS DATE)), 101), 
            ' - ', CONVERT(VARCHAR, DATEADD(DAY, 8 - DATEPART(WEEKDAY, 
            s.ServiceDate), CAST(s.ServiceDate AS DATE)), 101)) AS 
            WeekDateRange

FROM ServiceInfo AS s

The output:

ServiceDate	WeekDateRange
2022-01-03	01/03/2022 - 01/09/2022
2022-01-07	01/03/2022 - 01/09/2022
2022-01-15	01/10/2022 - 01/16/2022
2022-01-26	01/24/2022 - 01/30/2022
2022-01-29	01/24/2022 - 01/30/2022
2022-02-01	01/31/2022 - 02/06/2022
2022-02-04	01/31/2022 - 02/06/2022
2022-02-06	02/07/2022 - 02/13/2022 (Needs to be 01/31/2022 - 02/06/2022)
2022-02-07	02/07/2022 - 02/13/2022

Answer 1

You can use a reference date that is Monday to calculate the difference in days. Perform integer division by 7 and then multiply by 7 will gives you back no of days that starts on Monday

1st Jan, 1900 is a Monday, StartDate :

   DATEADD(DAY, DATEDIFF(DAY, '19000101', s.ServiceDate) / 7 * 7, '19000101') 

For End date, just add 1 week to the difference in days and subtract 1 day from the reference date which is 31 Dec 1899

   DATEADD(DAY, (DATEDIFF(DAY, '19000101', s.ServiceDate) / 7 + 1) * 7, '18991231') 

dbfiddle