TSQL日期条件语句的表现不符合预期

Question

我正在尝试过滤掉在特定时间范围内发生的一些事件。我已经尝试了许多变体，但我无法使过滤器按预期工作。下面的代码旨在当事件发生在晚上10点30分到早上7点30分之外时发送警报。

例如，如果事件发生在晚上11点34分，则应落入“无需操作”条件中，但实际上却落入了Else分支。
出现问题的示例日期与时间：2024年01月04日 23:34:42

DECLARE @actionTime AS smalldatetime = getdate(); -- 使用dateadd(hour, -16, getdate()) 获取昨天的时间
DECLARE @startTime AS smalldatetime, @endTime AS smalldatetime;

-- 设置开始时间和结束时间
SELECT @startTime = DATEADD(hour, 22, DATEADD(day, DATEDIFF(day, 0, getdate() - 1), 0)),
       @endTime = DATEADD(minute, 30, DATEADD(hour, 7, DATEADD(day, DATEDIFF(day, 0, getdate()), 0)));

PRINT @actionTime;
PRINT @endTime;
PRINT @startTime;

-- 检查动作时间是否在设定的时间范围内
IF (@actionTime >= @startTime AND @actionTime <= @endTime)
BEGIN
    -- 不执行任何操作
    PRINT '忽略警报';
END
ELSE
BEGIN
    PRINT '发送警报';
END

Doug Stevenson · Answer

尝试以下代码片段：

-- 计算昨日晚上10点30分的@startTime
SET @startTime = DATEADD(MINUTE, 30, DATEADD(HOUR, 22, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)))

-- 计算今日早上7点30分的@endTime
SET @endTime = DATEADD(MINUTE, 30, DATEADD(HOUR, 7, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)))

Steven · Answer

若使用SQL Server 2022版本，您可以结合使用DATETRUNC()函数和单个DATEADD()函数来计算起始和结束时间。

SET @startTime = DATEADD(minute, -1440 + 22 * 60 + 30, DATETRUNC(day, GETDATE()))
SET @endTime = DATEADD(minute, 7 * 60 + 30, DATETRUNC(day, GETDATE()))

（-1440分钟代表回溯一天。）

cHao · Answer

您确定结束时间应该包含在范围内吗？

无论是否包含结束时间，都可以轻松地将范围滑动90分钟，这样只需进行一次时间比较，而无需担心日期部分：

if cast(dateadd(minute, 90, getdate()) as time) <= cast('09:00am' as time)
begin
    ...
end

或者，您可以忽略90分钟的调整，单独比较两个时间段：

if cast(getdate() as time) >= cast('10:30pm' as time) or
   cast(getdate() as time) <= cast('07:30am' as time)

您也可以使用not between，不过需要注意微调边界，这种方法的好处是可以避免重复非字面表达式，有时这是有用的。当然，第一个选项也具有同样的特性：

if cast(getdate() as time) not between
    cast('10:30:59.9999999pm' as time) and cast('07:29:59.9999999am' as time)

观点补充：有些人主张完全避免使用between，因为他们认为专业软件开发人员可能难以理解离散类型如integer和date的工作原理，相对于更精确的类型如decimal和datetime。他们提出的正当观点之一是在处理此类范围时通常应采用半开区间。/观点补充

我应该早点意识到您遇到的问题是，当时间部分处于晚上10:30到午夜之间时，您的逻辑永远无法通过测试。虽然可以轻松计算出包括昨天一部分在内的范围，但显然由getdate()返回的日期+时间永远不会超出今天的范围。这也是您为何会注释掉那些片段并伪造当前时间的原因。