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 |
>Solution :
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')