How to get a date of day in current week

Question

Hi,

I have table that stores configurations like

In my sql code, I need to take currentDateTime and get

1. current week date for Execution Day which is Wednesday. So, for this week, it will be 10-02-2024.
2. Get following week wednesday date. Here it will be 10-09-2024
3. Get CutOffDate for that current week cutoffday (which is Friday). So, for this week it will be 10-04-2024
4. Then I need to see if currentdatetime (now) is less than cutoffdatetime (10-04-2024 17:00:00) if it is less than, then I should get

                Following Wednesday as nextexecution date should be following wednesday(10/09/2024)
      ```1. If currentdatetime > cuttoffdatetime (10-04-2024 17:00:00), for example currentdateTime is 10-04-2024 18:30:00) Then nextexecutiondate should be 2 weeks wednesday which is 10-16-2024.
   
   

Our sever is set up default to Sunday as start of the week.

How do I achieve this.

TIA.

Answer 1

Try this code, it eliminate the impact of the definition of first day of week.

DECLARE @current_datetime DATETIME
DECLARE @wd_today INT
DECLARE @date_thisWed DATE
DECLARE @date_thisFri DATE
DECLARE @time_cutoff TIME
DECLARE @datetime_thiscutoff DATETIME2
DECLARE @date_nextExec DATE
SET @time_cutoff = '17:00:00.0000000'  --Cutoff time from your configuration
SET @current_datetime = GETDATE()   --Current date & time
SET @wd_today = DATEPART(WEEKDAY, @current_datetime) + (@@DATEFIRST - 1)  
SET @wd_today = IIF( @wd_today<= 7, @wd_today, @wd_today - 7)   -- Today's week day, Mon. is 1, Tues. is 2...
SET @date_thisWed = DATEADD(DAY, 3 - @wd_today, @current_datetime)   --Date of this Wed.
SET @date_thisFri = DATEADD(DAY, 5 - @wd_today, @current_datetime)   --Date of this Fri.
SET @datetime_thiscutoff = DATETIME2FROMPARTS    --Get cutoff date & time of this week
	(
		DATEPART(year, @date_thisFri),
		DATEPART(month, @date_thisFri),
		DATEPART(day, @date_thisFri),
		DATEPART(hour, @time_cutoff),
		DATEPART(minute, @time_cutoff),
		DATEPART(second, @time_cutoff),
		DATEPART(nanosecond, @time_cutoff),
		7
	)
SET @date_nextExec = IIF( @current_datetime <= @datetime_thiscutoff, DATEADD(week, 1 , @date_thisWed), DATEADD(week, 2 , @date_thisWed))  -- Get next execution date
SELECT @date_nextExec