查询以查找特定时间段之间的记录

Question

我正在尝试编写一个SQL查询来获取特定时间段之间的数据。我想从2019-11-01到2019-12-01每天选择23：00到03：00之间的数据。

我写了一个如下所示的查询，

SELECT 
DATEPART(DAY, EnteredDate) AS PerDate,
COUNT(Item) AS ItemCount,
ItemCategory
FROM 
MyTable
WHERE 
EnteredDate BETWEEN '2019-11-01' AND '2012-12-01' AND 
CAST(EnteredDate AS TIME) BETWEEN '23:00:00' and '03:00:00'
GROUP BY 
DATEPART(EnteredDate), ItemCategory
ORDER BY 
DATEPART(DAY, EnteredDate)

任何人都可以帮助我进行一些我可以使用的查询吗？

Answer 1

我不确定这是否是您想要的，因为没有示例数据，但如果出现问题，请发表评论。

SELECT DATEPART(DAY, EnteredDate) AS PerDate
, COUNT(Item) AS ItemCount
, ItemCategory 
FROM MyTable 
WHERE EnteredDate BETWEEN '2019-11-01' AND '2019-12-01' 
AND  (DATEPART(HOUR, EnteredDate) in (23, 00, 01, 02)
or CAST(EnteredDate AS TIME) BETWEEN '00:00:01' and '03:00:00')
GROUP BY DATEPART(Day, EnteredDate)
, ItemCategory
ORDER BY DATEPART(DAY, EnteredDate) ;

这是一个演示

我不知道OP想要什么，我试着再猜一次：

SELECT cast(EnteredDate as date)
, ItemCategory 
,COUNT(Item) AS ItemCount
FROM MyTable 
WHERE EnteredDate BETWEEN '2019-11-01' AND '2019-12-01' 
AND  (DATEPART(HOUR, EnteredDate) in (23, 00, 01, 02)
or CAST(EnteredDate AS TIME) BETWEEN '00:00:01' and '03:00:00')
GROUP BY cast(EnteredDate as date)
, ItemCategory
ORDER BY cast(EnteredDate as date)
, ItemCategory;

这是演示

Answer 2

你的时间超过午夜。 我想你想把"班次"放在一起，所以从晚上 11：00 开始的时间被分配到第二天。

如果是这样，请添加一个小时并将其用于逻辑：

SELECT DATEPART(DAY, v.effective_date) AS PerDate,
COUNT(*) AS ItemCount,
ItemCategory
FROM MyTable t CROSS APPLY
(VALUES (DATEADD(hour, 1, t.EnteredDate))) v(effective_date)
WHERE v.effective_date >= '2019-11-01' AND
v.effective_date < '2012-12-01' AND 
CAST(EnteredDate AS TIME) NOT BETWEEN '03:00:00' and '23:00:00'
GROUP BY DATEPART(DAY, v.effective_date), ItemCategory
ORDER BY DATEPART(DAY, v.effective_date), ItemCategory

Answer 3

我相信这会对你有所帮助.

SELECT 
DATEPART(DAY, EnteredDate) AS PerDate,
COUNT(Item) AS ItemCount,
ItemCategory
FROM 
MyTable
WHERE 
CONVERT(DATE,EnteredDate) BETWEEN '2019-11-01' AND '2012-12-01' AND 
CAST(EnteredDate AS TIME) BETWEEN '23:00:00' and '03:00:00'
GROUP BY 
DATEPART(DAY, EnteredDate) , ItemCategory
ORDER BY 
DATEPART(DAY, EnteredDate)