'Get the last Wednesday from a given date

If a datetime field is passed, how do I determine when the last Wednesday was and set it to 10AM on that day?

Declare DateTime @datetime

For example:
if I pass in @datetime = '2022-Feb-7 5:00:00' then I should get 2022-Feb-2 10:00:00
if I pass in @datetime = '2022-Feb-10 16:00:00' then I should get 2022-Feb-9 10:00:00

I have an edge case here:
if the @datetime is Wednesday and the time is less than 10AM then it should set it to last Wednesday and if the time is greater than 10AM then it should set it current Wednesday 10AM.

For example:
if I pass in @datetime='2022-Feb-9 5:00:000' then I should get 2022-Feb-2 10:00:00
if I pass in @datetime = '2022-Feb-9 16:00:00' then I should get 2022-Feb-9 10:00:00

sqlsql-servertsql


Solution 1:[1]

You can calculate the previous Wednesday 10 AM like this:

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;

Demo on db<>fiddle here

How it works

The 10 hours is first subtracted from the @DateTime to get the cutoff time effect on a Wednesday.
@DateTime-'10:00' So after 10 AM on a Wednesday it calculates current Wednesday.

Now, to calculate the first day of the week, you could subtract the weekday+1 from the date.
But the first day of the week depends on the DATEFIRST setting. If it's 1 then Monday, if 7 then it's Sunday. So the variable @@datefirst is used to normalize the calculation. So that the calculation doesn't depend on the DATEFIRST setting.
This will get the previous Saturday:
@datetime-(0+@@datefirst+DATEPART(weekday,@datetime))%7
So this gets the previous Wednesday:
@datetime-(3+@@datefirst+DATEPART(weekday,@datetime))%7
The modulus 7 (%7) makes sure that it can't subtract more than 6.

--
-- 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 top 16
  [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
2022-02-01 11:00 3 Tuesday -6 2022-01-26 10:00:00.000 7
2022-02-02 11:00 4 Wednesday 0 2022-02-02 10:00:00.000 7
2022-02-03 11:00 5 Thursday -1 2022-02-02 10:00:00.000 7
2022-02-04 11:00 6 Friday -2 2022-02-02 10:00:00.000 7
2022-02-05 11:00 7 Saturday -3 2022-02-02 10:00:00.000 7
2022-02-06 11:00 1 Sunday -4 2022-02-02 10:00:00.000 7
2022-02-07 11:00 2 Monday -5 2022-02-02 10:00:00.000 7
2022-02-08 11:00 3 Tuesday -6 2022-02-02 10:00:00.000 7
2022-02-09 11:00 4 Wednesday 0 2022-02-09 10:00:00.000 7
2022-02-10 11:00 5 Thursday -1 2022-02-09 10:00:00.000 7
2022-02-11 11:00 6 Friday -2 2022-02-09 10:00:00.000 7
2022-02-12 11:00 7 Saturday -3 2022-02-09 10:00:00.000 7
2022-02-13 11:00 1 Sunday -4 2022-02-09 10:00:00.000 7
2022-02-14 11:00 2 Monday -5 2022-02-09 10:00:00.000 7
2022-02-15 11:00 3 Tuesday -6 2022-02-09 10:00:00.000 7
2022-02-16 11:00 4 Wednesday 0 2022-02-16 10:00:00.000 7

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions