SQL where 子句中的日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1947436/
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
Datetime in where clause
提问by Novice Developer
How can I select 12/20/2008 in whereclause of sql?
如何在wheresql 子句中选择 12/20/2008 ？
The server is SQL server 2005.
服务器是 SQL Server 2005。
select * from tblErrorLog
where errorDate = '12/20/2008'
回答by LukeH
WHERE datetime_column >= '20081220 00:00:00.000'
AND datetime_column < '20081221 00:00:00.000'
回答by marc_s
First of all, I'd recommend using the ISO-8601 standard format for date/time - it works regardless of the language and regional settings on your SQL Server. ISO-8601 is the YYYYMMDDformat - no spaces, no dashes - just the data:
首先，我建议对日期/时间使用 ISO-8601 标准格式 - 无论 SQL Server 上的语言和区域设置如何，它都可以工作。ISO-8601 是YYYYMMDD格式 - 没有空格，没有破折号 - 只是数据：
select * from tblErrorLog
where errorDate = '20081220'
Second of all, you need to be aware that SQL Server 2005 DATETIMEalways includes a time. If you check for exact match with just the date part, you'll get only rows that match with a time of 0:00:00 - nothing else.
其次，您需要知道 SQL Server 2005DATETIME总是包含时间。如果您只检查日期部分是否完全匹配，您将只得到与 0:00:00 时间匹配的行 - 没有别的。
You can either use any of the recommend range queries mentioned, or in SQL Server 2008, you could use the DATEonly date time - or you could do a check something like:
您可以使用提到的任何推荐范围查询，或者在 SQL Server 2008 中，您可以使用DATE唯一的日期时间 - 或者您可以执行以下检查：
select * from tblErrorLog
where DAY(errorDate) = 20 AND MONTH(errorDate) = 12 AND YEAR(errorDate) = 2008
Whichever works best for you.
哪个最适合你。
If you need to do this query often, you could either try to normalize the DATETIMEto include only the date, or you could add computed columns for DAY, MONTH and YEAR:
如果您需要经常执行此查询，您可以尝试将 标准化DATETIME为仅包含日期，或者您可以为 DAY、MONTH 和 YEAR 添加计算列：
ALTER TABLE tblErrorLog
ADD ErrorDay AS DAY(ErrorDate) PERSISTED
ALTER TABLE tblErrorLog
ADD ErrorMonth AS MONTH(ErrorDate) PERSISTED
ALTER TABLE tblErrorLog
ADD ErrorYear AS YEAR(ErrorDate) PERSISTED
and then you could query more easily:
然后您可以更轻松地查询：
select * from tblErrorLog
where ErrorMonth = 5 AND ErrorYear = 2009
and so forth. Since those fields are computed and PERSISTED, they're always up to date and always current, and since they're peristed, you can even index them if needed.
等等。由于这些字段是计算和持久化的，因此它们始终是最新的并且始终是最新的，并且由于它们是持久化的，您甚至可以在需要时对它们进行索引。
回答by J__
You don't say which database you are using but in MS SQL Server it would be
你没有说你使用的是哪个数据库，但在 MS SQL Server 中它会是
WHERE DateField = {d '2008-12-20'}
If it is a timestamp field then you'll need a range:
如果它是时间戳字段，那么您将需要一个范围：
WHERE DateField BETWEEN {ts '2008-12-20 00:00:00'} AND {ts '2008-12-20 23:59:59'}
回答by onupdatecascade
Assuming we're talking SQL Server DateTime
假设我们在谈论 SQL Server DateTime
Note: BETWEEN includes bothends of the range, so technically this pattern will be wrong:
注：BETWEEN包括两个范围的两端，所以在技术上这种模式将是错误的：
errorDate BETWEEN '12/20/2008' AND '12/21/2008'
My preferred method for a time range like that is:
对于这样的时间范围，我的首选方法是：
'20081220' <= errorDate AND errordate < '20081221'
Works with common indexes (range scan, SARGable, functionless) and correctly clips off midnight of the next day, without relying on SQL Server's time granularity (e.g. 23:59:59.997)
使用通用索引（范围扫描、SARGable、无功能）并正确剪掉第二天的午夜，而不依赖于 SQL Server 的时间粒度（例如 23:59:59.997）
回答by Middletone
Use a convert function to get all entries for a particular day.
使用转换函数获取特定日期的所有条目。
Select * from tblErrorLog where convert(date,errorDate,101) = '12/20/2008'
See CAST and CONVERTfor more info
有关详细信息，请参阅CAST 和 CONVERT
回答by Meff
select * from tblErrorLog
where errorDate BETWEEN '12/20/2008' AND DATEADD(DAY, 1, '12/20/2008')
