I have code that runs every weekday looking for the previous day, however, on Mondays, it needs to look from last Friday through Sunday. Right now, I am manually commenting in and out rows of code if it's a Monday. How can I have it combined without having to touch it every Monday? I use placeholders to declare dates that will be used further down the code. I am using SQL Server.
/* If today *is not* MONDAY, then use lines 2&3; if today *is* MONDAY, use lines 4&5 */
DECLARE @STARTDATE DATE = getdate()-1
DECLARE @PREV15STARTDATE DATE = getdate()-15
DECLARE @STARTDATE DATE = getdate()-3
DECLARE @PREV15STARTDATE DATE = getdate()-17
Later on, I will have a select statement that uses the following:
where ACTIVITY_CREATE_DATE_TIME between @PREV15STARTDATE and @STARTDATE
Determine the weekday for today, if it is Monday, subtract an extra two days from both. It can be tricky to reliably determine the proper weekday (say, Monday) because @@DATEFIRST settings can shift what DATEPART says, and @@LANGUAGE settings can shift what DATENAME says. So we can use a little modulo trick to get a 0-6, where 1 is always Monday regardless of session settings. Then a CASE expression can just dictate whether we need to shift by 2 days or not at all.
DECLARE @d date = GETDATE();
DECLARE @wd tinyint =
(DATEPART(WEEKDAY, @d) + @@DATEFIRST - 1) % 7;
DECLARE @shift tinyint =
CASE @wd WHEN 1 THEN 2 ELSE 0 END;
DECLARE @STARTDATE date = DATEADD(DAY, -1-@shift, @d),
@PREV15STARTDATE date = DATEADD(DAY, -15-@shift, @d);
If you are anti-0-based and want to use 1-7 (Monday = 1, Sunday = 7) then you can change the expression to:
((DATEPART(WEEKDAY, @d) + @@DATEFIRST - 2) % 7) + 1;
P.S. it's highly recommended to stay away from "date math" like getdate()-{integer}, since it will break if you swap in different types. Use DATEADD even though it's a little more work.