如何在午夜之间变换2次之间查询时间数据类型 [英] How do I query time data type with between 2 times varying over midnight

问题描述

如何查询时间数据类型，午夜时间变化2次。

这是我尝试过的。

How do I query time data type with between 2 times varying over midnight.
Here is what I have tried.

declare @timeValue time
SET @timeValue = '23:30:00'

SELECT @timeValue,DATEADD(minute, -30, @timeValue),DATEADD(minute, +30, @timeValue)
WHERE @timeValue BETWEEN DATEADD(minute, -30, @timeValue) AND DATEADD(minute, +30, @timeValue)

但是如果我们采用上面的例子来确定时间在30分钟之前和之后是否有效，并且它会在午夜之间变化。我没有得到预期的结果。



预期的结果将是timeValue变量值的行以及其他列之前和之后的30分钟。



我尝试了什么：



如果我使用的时间没有越过午夜就像它下面的一个正常工作。

But if we take the above example for finding if the time is valid in between 30 mins before and after, and it it varies over midnight. I don't get expected result.

Expected result would be the row with value in timeValue variable along with 30 mins before and after as other columns.

What I have tried:

If I use the time which is not crossing over the midnight like the one below it works properly.

declare @timeValue time
SET @timeValue = '23:00:00'

SELECT @timeValue,DATEADD(minute, -30, @timeValue),DATEADD(minute, +30, @timeValue)
WHERE @timeValue BETWEEN DATEADD(minute, -30, @timeValue) AND DATEADD(minute, +30, @timeValue)

我得到了预期的以下响应。



---------------- ---------------- - --------------

23：00：00.0000000 22：30：00.0000000 23：30：00.0000000

And I get the following response as expected.

---------------- ---------------- ----------------
23:00:00.0000000 22:30:00.0000000 23:30:00.0000000

推荐答案

午夜时间。换句话说：23：30 + 00:30 = 00:00，低于23:30。条件23:00< 23:30< 00:00不符合。解决方法是将参数转换为datetime：

Time wraps at midnight. In other words: 23:30 + 00:30 = 00:00 which is less than 23:30. Condition 23:00 < 23:30 < 00:00 is not met. Workaround is to convert arguments to datetime:

declare @timeValue time
SET @timeValue = '23:30:00'

SELECT @timeValue,DATEADD(minute, -30, @timeValue),DATEADD(minute, +30, @timeValue)
WHERE CONVERT(datetime,@timeValue) BETWEEN DATEADD(minute, -30, CONVERT(datetime,@timeValue)) AND DATEADD(minute, +30, CONVERT(datetime,@timeValue))

这篇关于如何在午夜之间变换2次之间查询时间数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！