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

CodePudding user response：

Given this data:

CREATE TABLE #dates(source smalldatetime);

INSERT #dates(source) VALUES
('20220207 05:00:00'), -- should be 2/2
('20220210 16:00:00'), -- should be 2/9
('20220209 05:00:00'), -- should be 2/2
('20220209 16:00:00'), -- should be 2/9
('20220209 09:59:00'), -- should be 2/2
('20220209 10:00:00'); -- should be 2/9

This query will shift the source datetime value by 14 hours so that anything from 10 AM on will technically be considered the next day. This "simplifies" the calculation and allows us to subtract an additional week only in the case where the day is Wednesday and the adjusted time is still on Wednesday. Works for any SET DATEFIRST n setting.

SELECT source, prev_wed = DATEADD(HOUR, 10, DATEADD(DAY, 
  COALESCE(NULLIF((-@@DATEFIRST-DATEPART(WEEKDAY,adj)-3)%7,0),-7),adj))
FROM 
(
  SELECT source, adj = CONVERT(smalldatetime, 
    CONVERT(date, DATEADD(HOUR, 14, source)))
  FROM #dates
) AS adj;

Results (example db<>fiddle):

A slightly simpler way that avoids the @@DATEFIRST complication is to take a known Wednesday in the past, and see how many 7-day intervals have happened since then.

DECLARE @base date = '20200101'; -- known Wednesday

SELECT source, prev_wed = DATEADD(DAY,DATEDIFF(DAY,@base,
    CONVERT(date, DATEADD(HOUR, -10, source)))/7*7, @base)
FROM #dates;

Results are the same (db<>fiddle).

CodePudding user response：

You can calculate the last Wednesday like this:

DECLARE @PreviousWednesday DATETIME;

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

Demo on db<>fiddle here