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
30
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
5
select *
from MyTable
where CAST(Created as time) not between '07:00' and '22:59:59 997'
#3
1
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
#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
30
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
5
select *
from MyTable
where CAST(Created as time) not between '07:00' and '22:59:59 997'
#3
1
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
#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'