检查时间是否在两次之间（时间数据类型）

I have a table that has a column "Created" as a datetime.

我有一个表“Created”作为日期时间。

I'm trying to query to check if the time for the Created value is between two times.

我正在尝试查询以检查Created值的时间是否在两次之间。

The Created datetime for the first row is '2013-07-01 00:00:00.000' (Midnight) and I'm trying to query for items with a time between 11PM and 7AM.

第一行的创建日期时间是'2013-07-01 00：00：00.000'（午夜），我正在尝试查询时间在晚上11点到早上7点之间的项目。

select *
from MyTable
where CAST(Created as time) between '23:00:00' and '06:59:59'

But no results are returned.

但是没有返回任何结果。

Do I need to convert my times to datetimes?

我需要将时间转换为日期时间吗？

4 个解决方案

#1

I suspect you want to check that it's after 11pm or before 7am:

我怀疑你要检查它是在晚上11点之后还是在早上7点之前：

select *
from MyTable
where CAST(Created as time) >= '23:00:00' 
   or CAST(Created as time) < '07:00:00'

#2

select *
from MyTable
where CAST(Created as time) not between '07:00' and '22:59:59 997'

#3

This should also work (even in SQL-Server 2005):

这也应该工作（即使在SQL-Server 2005中）：

SELECT *
FROM dbo.MyTable
WHERE Created >= DATEADD(hh,23,DATEADD(day, DATEDIFF(day, 0, Created - 1), 0))
  AND Created <  DATEADD(hh,7,DATEADD(day, DATEDIFF(day, 0, Created), 0))

DEMO

DEMO

#4

-1

Should be AND instead of OR

应该是AND而不是OR

select *
from MyTable
where CAST(Created as time) >= '23:00:00' 
   AND CAST(Created as time) < '07:00:00'

#1