T-SQL如何选择DATETIME=周末和下班后的工作日的记录

Question

我有一个表，它有一个名为[Date]的DATETIME列。我只需要选择[Date]列为周日和周六的记录，以及周一至周五上午12:00至上午8:00和下午5:00至凌晨12:00的记录。

这是我的代码：

SELECT *
FROM table
WHERE (DATEPART(weekday, [Date]) IN (1, 7)
       OR (DATEPART(weekday, [Date]) IN (2, 3, 4, 5, 6)
           AND (CAST([Date] AS TIME) BETWEEN '12:00am'
                                     AND     '8:00am'
                OR CAST([Date] AS TIME) BETWEEN '5:00pm'
                                        AND     '12:00:00am')))

当我检查结果时，我发现表中缺少一些"下班后"中有[日期]的记录。我的代码出了什么问题？

Answer 1

有时格式化代码可以帮助您更好地查看逻辑（和/或）分组。

WHERE 
(DATEPART(weekday, [Date]) IN (1, 7)) --<-- Put an extra closing paren here to isolate this side of the disjunctive (OR)
OR
(
   DATEPART(weekday, [Date]) IN (2, 3, 4, 5, 6)
   AND (
          (CAST([Date] AS TIME) BETWEEN '12:00am' AND '8:00am') --<--the first and last paren are not necessary, but help you to visually isolate this side of the disjunctive (OR)
          OR 
          (CAST([Date] AS TIME) BETWEEN '5:00pm' AND '12:00:00am') --<-- same here
       )
)

Answer 2

我想明白了。我把最后的"12:00:00am"改为"11:59:59pm"。谢谢你抽出时间。