0

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
lifeisajourney
  • 117
  • 4
  • 2
    Your `DECLARE` syntax is incorrect, it should be `DECLARE @datetime datetime` (not `DECLARE datetime @datetime`) - that said, you should avoid the deprecated `datetime` type and instead prefer `datetime2` or `datetimeoffset`. – Dai Feb 07 '22 at 23:05

2 Answers2

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
LukStorms
  • 28,916
  • 5
  • 31
  • 45
1

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):

source prev_wed
2022-02-07 05:00 2022-02-02 10:00
2022-02-10 16:00 2022-02-09 10:00
2022-02-09 05:00 2022-02-02 10:00
2022-02-09 16:00 2022-02-09 10:00
2022-02-09 09:59 2022-02-02 10:00
2022-02-09 10:00 2022-02-09 10:00

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).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks, Aaron! Could you please explain what's happening here? `(-@@DATEFIRST-DATEPART(WEEKDAY,adj)-3)%7,0),-7)`. I'm confused with this part. – lifeisajourney Feb 08 '22 at 03:53
  • @lifeisajourney You need to deal with any potential `@@DATEFIRST` setting so this expression basically “normalizes” it by using mod 7 after subtracting 3 days from the weekday shown by the date. This translates it to Wednesday. Not a very good explanation but this is a comment and the second, simpler solution is probably the one you should use if you need to fully understand the mechanics. – Aaron Bertrand Feb 08 '22 at 03:55
  • @lifeisajourney I think one of the better pages here about day of week and datefirst is https://stackoverflow.com/q/7168874/61305 – Aaron Bertrand Feb 08 '22 at 03:59