Last Sunday until this upcoming Sunday with time

I am trying to retrieve the last recent Sunday at 3 AM until this upcoming Sunday 2:59 AM.

Below is what I have but it doesn't retrieve exactly the upcoming Sunday. Ideally, whenever I run this query, it should provide me with the expected result. For example, if I run this query today, it should return StartDate: 8/25/2024 3:00 AM and EndDate: 9/1/2024 2:59 AM while if I run next week, it should provide me with StartDate: 9/1/2024 3:00 AM and EndDate: 9/8/2024 2:59 AM

One main concern is, if I were to run this query on a Sunday. Some places have the week start on Sunday while others on Monday so the query should take that into account and have the result of being Sunday - Sunday.

select dateadd(hour,3,dateadd(minute, 00, DATEADD(dd,  DATEDIFF(dd,-1,GETDATE())/7*7,-1))) StartDate,
dateadd(hour,+2,dateadd(minute, +59, dateadd(day, ((15-@@datefirst) - datepart(dw, getdate())) % 7, convert(char(12), getdate()))))  EndDate

First, find today at 3 AM:

DECLARE @today datetime = DATEADD(HOUR, 3, 
   CONVERT(datetime, CONVERT(date, GETDATE())));

Then take the day of the week you want (in this case Sunday = 1), and calculate the previous one. This formula works regardless of DATEFIRST setting.

DECLARE @PreviousSunday datetime = DATEADD(DAY, 
   1 - (DATEPART(WEEKDAY, @today) + @@DATEFIRST) % 7, @today);

Now you just need to add a week to get the other bound (and subtract a minute using DATEADD again if you must, but this is ill-advised since you will miss data that happens between 2:59 and 3:00).

SELECT StartRange = @PreviousSunday,
       ProperEnd  = DATEADD(DAY, 7, @PreviousSunday),
       HokeyEnd   = DATEADD(MINUTE, -1, DATEADD(DAY, 7, @PreviousSunday));

All together:

DECLARE @today datetime = DATEADD(HOUR, 3, 
   CONVERT(datetime, CONVERT(date, GETDATE())));

DECLARE @PreviousSunday datetime = DATEADD(DAY, 
   1 - (DATEPART(WEEKDAY, @today) + @@DATEFIRST) % 7, @today);

SELECT StartRange = @PreviousSunday,
       ProperEnd  = DATEADD(DAY, 7, @PreviousSunday),
       HokeyEnd   = DATEADD(MINUTE, -1, DATEADD(DAY, 7, @PreviousSunday));

Results:

• db<>fiddle

If you want Monday instead of Sunday, just change 1 - on line 4 to 2 -:

• db<>fiddle