獲取給定日期的最后一個星期三

如果傳遞了日期時間欄位，我如何確定上一個星期三的時間并將其設定為當天上午 10 點？

Declare DateTime @datetime

例如：
如果我通過，@datetime = '2022-Feb-7 5:00:00'那么我應該得到2022-Feb-2 10:00:00
如果我通過，@datetime = '2022-Feb-10 16:00:00'那么我應該得到2022-Feb-9 10:00:00

我在這里有一個極端情況：
如果@datetime是星期三并且時間小于上午 10 點，則應將其設定為上周三，如果時間大于上午 10 點，則應將其設定為當前的星期三上午 10 點。

例如：
如果我通過，@datetime='2022-Feb-9 5:00:000'那么我應該得到2022-Feb-2 10:00:00
如果我通過，@datetime = '2022-Feb-9 16:00:00'那么我應該得到2022-Feb-9 10:00:00

uj5u.com熱心網友回復：

鑒于此資料：

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

此查詢會將源日期時間值移動 14 小時，因此從技術上講，從上午 10 點開始的任何時間都將被視為第二天。這“簡化”了計算，并允許我們僅在當天是星期三且調整后的時間仍然是星期三的情況下減去額外的一周。適用于任何SET DATEFIRST n設定。

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;

結果（例如db<>fiddle）：

避免@@DATEFIRST并發癥的一種稍微簡單的方法是在過去的一個已知的星期三，看看從那時起發生了多少個 7 天的間隔。

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;

結果是相同的（db<>fiddle）。

uj5u.com熱心網友回復：

您可以像這樣計算上一個星期三上午 10 點：

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;

關于db<>fiddle的演示在這里

怎么運行的

首先從 @DateTime 中減去 10 小時，以獲得星期三的截止時間效果。
@DateTime-'10:00' 因此，在星期三上午 10 點之后，它會計算當前的星期三。

現在，要計算一周的第一天，您可以從日期中減去作業日 1。
但一周的第一天取決于DATEFIRST設定。如果是 1 則為星期一，如果為 7 則為星期日。所以變數@@datefirst 用于標準化計算。這樣計算不依賴于 DATEFIRST 設定。
這將得到上一個星期六：
@datetime-(0 @@datefirst DATEPART(weekday,@datetime))%7
所以這得到上一個星期三：
@datetime-(3 @@datefirst DATEPART(weekday,@datetime))%7
模數 7 ( %7) 確保它不能減去超過 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	作業日	作業日名稱	日差	上一個星期三	df
2022-02-01 11:00	3	周二	-6	2022-01-26 10:00:00.000	7
2022-02-02 11:00	4	周三	0	2022-02-02 10:00:00.000	7
2022-02-03 11:00	5	周四	-1	2022-02-02 10:00:00.000	7
2022-02-04 11:00	6	星期五	-2	2022-02-02 10:00:00.000	7
2022-02-05 11:00	7	周六	-3	2022-02-02 10:00:00.000	7
2022-02-06 11:00	1	星期日	-4	2022-02-02 10:00:00.000	7
2022-02-07 11:00	2	周一	-5	2022-02-02 10:00:00.000	7
2022-02-08 11:00	3	周二	-6	2022-02-02 10:00:00.000	7
2022-02-09 11:00	4	周三	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

標籤：sql sql服务器 tsql

上一篇：如何將OUTPUTDELETED的結果分配給輸出引數？

下一篇：有沒有更好的方法在T-SQL中按類別開始和結束時間匯總時間序列？