SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @DateTime DATETIME = GETDATE();
DECLARE @LastWednesday DATETIME;

SET @LastWednesday = CAST(DATEADD(day, -(3+@@DATEFIRST+DATEPART(weekday,@DateTime-'10:00'))%7, 
CAST(@DateTime-'10:00' AS DATE)) AS DATETIME)+'10:00';

SELECT @DateTime, @LastWednesday;


(No column name)
(No column name)


2024-09-12 23:03:39.643
2024-09-11 10:00:00.000


--
-- Test over date range
--
DECLARE @StartDatetime SMALLDATETIME;
DECLARE @EndDatetime SMALLDATETIME;
SET @StartDateTime = DATEADD(month, -1, DATEADD(day, 1, EOMONTH(GETDATE())));
SET @EndDatetime = EOMONTH(@StartDatetime);

with cte as (
  select @StartDatetime+'11:00' as [Dt]
  union all
  select dateadd(day, 1, [Dt]) from cte
  where [Dt] < @EndDatetime
)
select
  [Dt] 
, datepart(weekday, [Dt]) AS weekday
, datename(weekday, [Dt]) AS weekdayname
, -(3+@@DATEFIRST+DATEPART(weekday,[Dt]-'10:00'))%7 AS daydiff
, prevWed = CAST(DATEADD(day,-(3+@@DATEFIRST+DATEPART(weekday,[Dt]-'10:00'))%7,CAST([Dt]-'10:00' AS DATE)) AS DATETIME)+'10:00'
, @@DATEFIRST AS df
from cte;



Dt
weekday
weekdayname
daydiff
prevWed
df


2024-09-01 11:00
1
Sunday
-4
2024-08-28 10:00:00.000
7

2024-09-02 11:00
2
Monday
-5
2024-08-28 10:00:00.000
7

2024-09-03 11:00
3
Tuesday
-6
2024-08-28 10:00:00.000
7

2024-09-04 11:00
4
Wednesday
0
2024-09-04 10:00:00.000
7

2024-09-05 11:00
5
Thursday
-1
2024-09-04 10:00:00.000
7

2024-09-06 11:00
6
Friday
-2
2024-09-04 10:00:00.000
7

2024-09-07 11:00
7
Saturday
-3
2024-09-04 10:00:00.000
7

2024-09-08 11:00
1
Sunday
-4
2024-09-04 10:00:00.000
7

2024-09-09 11:00
2
Monday
-5
2024-09-04 10:00:00.000
7

2024-09-10 11:00
3
Tuesday
-6
2024-09-04 10:00:00.000
7

2024-09-11 11:00
4
Wednesday
0
2024-09-11 10:00:00.000
7

2024-09-12 11:00
5
Thursday
-1
2024-09-11 10:00:00.000
7

2024-09-13 11:00
6
Friday
-2
2024-09-11 10:00:00.000
7

2024-09-14 11:00
7
Saturday
-3
2024-09-11 10:00:00.000
7

2024-09-15 11:00
1
Sunday
-4
2024-09-11 10:00:00.000
7

2024-09-16 11:00
2
Monday
-5
2024-09-11 10:00:00.000
7

2024-09-17 11:00
3
Tuesday
-6
2024-09-11 10:00:00.000
7

2024-09-18 11:00
4
Wednesday
0
2024-09-18 10:00:00.000
7

2024-09-19 11:00
5
Thursday
-1
2024-09-18 10:00:00.000
7

2024-09-20 11:00
6
Friday
-2
2024-09-18 10:00:00.000
7

2024-09-21 11:00
7
Saturday
-3
2024-09-18 10:00:00.000
7

2024-09-22 11:00
1
Sunday
-4
2024-09-18 10:00:00.000
7

2024-09-23 11:00
2
Monday
-5
2024-09-18 10:00:00.000
7

2024-09-24 11:00
3
Tuesday
-6
2024-09-18 10:00:00.000
7

2024-09-25 11:00
4
Wednesday
0
2024-09-25 10:00:00.000
7

2024-09-26 11:00
5
Thursday
-1
2024-09-25 10:00:00.000
7

2024-09-27 11:00
6
Friday
-2
2024-09-25 10:00:00.000
7

2024-09-28 11:00
7
Saturday
-3
2024-09-25 10:00:00.000
7

2024-09-29 11:00
1
Sunday
-4
2024-09-25 10:00:00.000
7

2024-09-30 11:00
2
Monday
-5
2024-09-25 10:00:00.000
7


SET DATEFIRST 7;
DECLARE @DateTime SMALLDATETIME = GETDATE();
DECLARE @previousDay SMALLDATETIME;

SET @previousDay = @DateTime-(0+@@DATEFIRST+DATEPART(weekday,@DateTime))%7;

SELECT
  @DateTime [DateTime]
, DATENAME(weekday, @DateTime) AS DateDayName
, @previousDay AS previousDay
, DATENAME(weekday, @previousDay) AS previousDayName
, @@datefirst AS datefirst;


DateTime
DateDayName
previousDay
previousDayName
datefirst


2024-09-12 23:04
Thursday
2024-09-07 23:04
Saturday
7

Dt	weekday	weekdayname	daydiff	prevWed	df
2024-09-01 11:00	1	Sunday	-4	2024-08-28 10:00:00.000	7
2024-09-02 11:00	2	Monday	-5	2024-08-28 10:00:00.000	7
2024-09-03 11:00	3	Tuesday	-6	2024-08-28 10:00:00.000	7
2024-09-04 11:00	4	Wednesday	0	2024-09-04 10:00:00.000	7
2024-09-05 11:00	5	Thursday	-1	2024-09-04 10:00:00.000	7
2024-09-06 11:00	6	Friday	-2	2024-09-04 10:00:00.000	7
2024-09-07 11:00	7	Saturday	-3	2024-09-04 10:00:00.000	7
2024-09-08 11:00	1	Sunday	-4	2024-09-04 10:00:00.000	7
2024-09-09 11:00	2	Monday	-5	2024-09-04 10:00:00.000	7
2024-09-10 11:00	3	Tuesday	-6	2024-09-04 10:00:00.000	7
2024-09-11 11:00	4	Wednesday	0	2024-09-11 10:00:00.000	7
2024-09-12 11:00	5	Thursday	-1	2024-09-11 10:00:00.000	7
2024-09-13 11:00	6	Friday	-2	2024-09-11 10:00:00.000	7
2024-09-14 11:00	7	Saturday	-3	2024-09-11 10:00:00.000	7
2024-09-15 11:00	1	Sunday	-4	2024-09-11 10:00:00.000	7
2024-09-16 11:00	2	Monday	-5	2024-09-11 10:00:00.000	7
2024-09-17 11:00	3	Tuesday	-6	2024-09-11 10:00:00.000	7
2024-09-18 11:00	4	Wednesday	0	2024-09-18 10:00:00.000	7
2024-09-19 11:00	5	Thursday	-1	2024-09-18 10:00:00.000	7
2024-09-20 11:00	6	Friday	-2	2024-09-18 10:00:00.000	7
2024-09-21 11:00	7	Saturday	-3	2024-09-18 10:00:00.000	7
2024-09-22 11:00	1	Sunday	-4	2024-09-18 10:00:00.000	7
2024-09-23 11:00	2	Monday	-5	2024-09-18 10:00:00.000	7
2024-09-24 11:00	3	Tuesday	-6	2024-09-18 10:00:00.000	7
2024-09-25 11:00	4	Wednesday	0	2024-09-25 10:00:00.000	7
2024-09-26 11:00	5	Thursday	-1	2024-09-25 10:00:00.000	7
2024-09-27 11:00	6	Friday	-2	2024-09-25 10:00:00.000	7
2024-09-28 11:00	7	Saturday	-3	2024-09-25 10:00:00.000	7
2024-09-29 11:00	1	Sunday	-4	2024-09-25 10:00:00.000	7
2024-09-30 11:00	2	Monday	-5	2024-09-25 10:00:00.000	7