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

debugcn 发表于 Dev

乌代亚

我试图编写一个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)

有人可以帮我查询一些我可以使用的吗？

博卡

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

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;

这是演示

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。