具有移动日期的 SQL 特定时间

您可以使用30分钟获得上周六的时间，并6*60在 6 小时内获得几分钟（您可以通过几种方式获得，但我使用它进行了硬编码：

DATEADD(MINUTE,(30 + (6 * 60) ), DATEADD(WEEK, DATEDIFF(WEEK, -1, CURRENT_TIMESTAMP), -2))

然后你可以在 where 子句中使用它：

SELECT 
    *
FROM dbo.Data AS d
WHERE 
    d.TimeStamp <= DATEADD(MINUTE,(30 + (6 * 60) ), DATEADD(WEEK, DATEDIFF(WEEK, -1, CURRENT_TIMESTAMP), -2))

更新：TLDR；使用示例查询将其分解：（希望它有所帮助）

DECLARE @MyDatetime AS DATETIME = '2021-09-12 03:02:05.257'  --CURRENT_TIMESTAMP;
SELECT
@MyDatetime AS FromWhen,
/* all these are The int difference between the startdate and enddate, expressed in the boundary set by datepart (WEEK)) */
DATEDIFF(WEEK, 0, @MyDatetime) AS MyWeek0, 
DATEDIFF(WEEK, -1, @MyDatetime) AS MyWeek1, 
DATEDIFF(WEEK, -2, @MyDatetime) AS MyWeek2, 
DATEDIFF(WEEK, -3, @MyDatetime) AS MyWeek3, 
DATEDIFF(WEEK, -4, @MyDatetime) AS MyWeek4, 
DATEDIFF(WEEK, -5, @MyDatetime) AS MyWeek5, 
DATEDIFF(WEEK, -6, @MyDatetime) AS MyWeek6, 
DATEDIFF(WEEK, -7, @MyDatetime) AS MyWeek7, 
DATEDIFF(WEEK, -8, @MyDatetime) AS MyWeek8, 
DATEDIFF(WEEK, -1, @MyDatetime) AS AWeekBack, -- get the week a week from RightNow DATEDIFF ( datepart , startdate , enddate )  
DATEADD(WEEK, DATEDIFF(WEEK, -1, @MyDatetime), -2) AS Saturday0000, -- Get the Saturday date from that (-2 is saturday, -1 sunday, ...-7 monday)
DATEADD(MINUTE,(30 + (6 * 60) ), DATEADD(WEEK, DATEDIFF(WEEK, -1, @MyDatetime), -2)) AS Saturday0630 -- now add the minutes for 6:30 AM to that saturday date

该查询中给定集合的值：

FromWhen MyWeek0 MyWeek1 MyWeek2 MyWeek3 MyWeek4 MyWeek5 MyWeek6 MyWeek7 MyWeek8 AWeekBack 星期六0000 星期六0630

2021-09-12 03:02:05.257 6350 6350 6351 6351 6351 6351 6351 6351 6351 6350 2021-09-11 00:00:00.03:03010:00.010

分解：从 NOW DATEDIFF 返回一周 (-1) 始终使用星期日作为一周的第一天，以确保函数以确定性方式运行，这 -2 是星期六。

DATEDIFF(WEEK, -1, CURRENT_TIMESTAMP)

例如，这是我经常使用的一个查询，从给定 DATETIME 列的 120 秒范围内获取那几千行：

SELECT *
FROM SomeTable AS st
WHERE
    st.DateColumn >= DATEADD(SECOND, -60, @SomeDateTime)
    AND st.DateColumn < DATEADD(SECOND, 60, @SomeDateTime)