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:
StartRange | ProperEnd | HokeyEnd |
---|---|---|
2024-08-25 03:00:00.000 | 2024-09-01 03:00:00.000 | 2024-09-01 02:59:00.000 |
If you want Monday instead of Sunday, just change 1 -
on line 4 to 2 -
: