SQL 使用 GETDATE()-1 和从/到的时间段查询前一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35602924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query previous day with GETDATE()-1 and a time period from/to
提问by Scott_K
I want to get total counts from SuperScottTable1from the previous day and narrow to a time frame (From-To). The below works fine until I add
我想SuperScottTable1从前一天获得总计数并缩小到一个时间范围（从到）。以下工作正常，直到我添加
AND (time > '08:00:00.000' AND time < '22:00:00.000')
It does not error, just returns null.
它没有错误，只是返回null。
Is this possible to do?
这是可能的吗？
SELECT
SUM(COALESCE(confirmedCount, 0))
FROM
SuperScottTable1
WHERE
superLaneID = '90099'
AND time >= GETDATE()-1
AND (time > '08:00:00.000' AND time < '22:00:00.000')
回答by kicken
You can do some CAST/CONVERT shenanigans to generate a starting and stoping DATETIME value which you can then compare to your time column. If the time column is indexed then this will allow the server to do a simple range search on the index to find matches.
您可以执行一些 CAST/CONVERT 恶作剧来生成开始和停止 DATETIME 值，然后您可以将其与时间列进行比较。如果时间列被索引，那么这将允许服务器对索引进行简单的范围搜索以查找匹配项。
WHERE
superLaneID = '90099'
AND time > CAST(CONVERT(VARCHAR(20), GETDATE()-1, 112) + ' 08:00:00' AS DATETIME)
AND time < CAST(CONVERT(VARCHAR(20), GETDATE()-1, 112) + ' 22:00:00' AS DATETIME)
回答by Used_By_Already
GETDATE() in SQL Server returns the system date and timeto arrive at the current day (i.e. date at time 00:00:00.0000000) you can use either of these:
SQL Server 中的 GETDATE() 返回系统日期和到达当天的时间（即时间为 00:00:00.0000000 的日期），您可以使用以下任一方法：
- cast(getdate() as date) -- available from SQL 2008 onward
- dateadd(day, datediff(day,0, getdate() ), 0) -- any SQL Server version
- cast(getdate() as date) -- 从 SQL 2008 开始可用
- dateadd(day, datediff(day,0, getdate() ), 0) -- 任何 SQL Server 版本
Using these to establish the current date, then add (8-24) hours and or (22-24) hours to establish the boundaries of yesterday e.g.
使用这些来确定当前日期，然后添加 (8-24) 小时和/或 (22-24) 小时来确定昨天的边界，例如
WHERE superLaneID = '90099'
AND time >= dateadd(hour,(8-24),cast(getdate() as date))
AND time < dateadd(hour,(22-24),cast(getdate() as date))
Avoid using non-sargable predicates (such as using a function) that require you to alter each row of data to compare to a fixed value. ref this prior answer
避免使用要求您更改每一行数据以与固定值进行比较的不可 sargable 谓词（例如使用函数）。参考这个先前的答案
Btw: This method discussed here would work for a time range that spans midnight.
顺便说一句：这里讨论的这种方法适用于跨越午夜的时间范围。
回答by Gordon Linoff
Presumably, timeis stored as a datetime. You seem to be using SQL Server, so you can extract the hour and use that:
据推测，time存储为datetime. 您似乎正在使用 SQL Server，因此您可以提取小时并使用它：
WHERE superLaneID = '90099' AND
time >= GETDATE()-1 AND
datepart(hour, time) >= 8 AND
datepart(hour, time) < 22
EDIT: To get the "other" hours:
编辑：要获得“其他”时间：
WHERE superLaneID = '90099' AND
time >= GETDATE()-1 AND
(datepart(hour, time) < 8 or
datepart(hour, time) >= 22
)
