SQL: capture start and end of 2 weeks ahead-CodePudding

I've looked through the similar questions and none of them seem to capture my use case.

What I'm needing to do is identify the start and end days of 2 weeks ahead, irrespective of the day of the week "today" might be. Note, weeks in this scenario start with Sunday and end with Saturday.

For example, the week of 12/11/2022 - 12/17/2022, let's say "today" is 12/13. I need my @Start and @End to be 12/25 and 12/31 respectively. Similarly, I need this same output if "today" were to fall on any day in that week (12/11 - 12/17).

The solution I was using previously involved the use of the week of the year a given date lived in and adding 2 to that number to identify my date range, but where I came up short of course was when the new year started.

CodePudding user response：

You can use DATEPART and DATEADD functions to achieve what you want, here is an example using your example date of 2022-12-13, but it will work for any valid date:

declare @today date = '2022-12-13'; --returns start: 2022-12-25, end: 2022-12-31
--declare @today date = '2022-12-26'; --returns start: 2023-01-08, end: 2023-01-14

--Check what day of the week the date you are working with is,
--if it's not Sunday, subtract days to get the current week's Sun. date
IF DATEPART(WEEKDAY, @today) = 3
    SET @today = DATEADD(day, -2, @today);
ELSE IF DATEPART(WEEKDAY, @today) = 4
    SET @today = DATEADD(day, -3, @today);
ELSE IF DATEPART(WEEKDAY, @today) = 5
    SET @today = DATEADD(day, -4, @today);
ELSE IF DATEPART(WEEKDAY, @today) = 6
    SET @today = DATEADD(day, -5, @today);
ELSE IF DATEPART(WEEKDAY, @today) = 7
    SET @today = DATEADD(day, -6, @today);
ELSE IF DATEPART(WEEKDAY, @today) = 2
    SET @today = DATEADD(day, -1, @today);

--add 14 days to the current week Sun date
declare @startdate date = DATEADD(day, 14, @today);
--add 6 days to the desired start date
declare @enddate date = DATEADD(day, 6, @startdate);

select @startdate, @enddate