在where子句中使用日期时间

Question

如何在 SQL 的 where 子句中选择 12/20/2008？

服务器是 SQL Server 2005。

select * from tblErrorLog
where errorDate = '12/20/2008'

- Novice Developer

---

“errorDate”列是什么数据类型？ - OMG Ponies

如果列数据类型确实为datetime，则上述代码应该可以正常工作。 - ryanulit

如果 SQL Server 上的日期格式未设置为美国格式，则上述代码将无法正常工作。请使用以下{ts}方法以实现地域独立性。 - J__

最好将日期转换并明确其格式/类型，因为某些系统和代码会以不同的方式读取日期，这样更安全。 - Middletone

Answer 1

WHERE datetime_column >= '20081220 00:00:00.000'
  AND datetime_column < '20081221 00:00:00.000'

- LukeH

---

如果你使用这种方法，它是不起作用的，日期应该是“2008-20-12”（年-日-月）。如果使用{ts}标识符，则这将是正确的格式。 - J__

@J: yyyy-MM-dd 格式在大多数情况下确实有效，但并非所有情况都适用。 @onupdatecascade: 你说得对。我已经更新了我的答案，使用了一个在所有情况下都安全的格式。 - LukeH

不要使用可能不具备未来性的 ,997，而应该使用 datetime_column >= '20081220' 和 datetime_column < '20081221' -- 这样也不会出现日期格式的问题，YYYYMMDD 可以正常工作。 - James Z

Answer 2

首先，我建议使用ISO-8601标准格式的日期/时间 - 它可以在SQL Server上不考虑语言和区域设置的情况下工作。ISO-8601是YYYYMMDD格式 - 没有空格，没有破折号 - 只有数据：

select * from tblErrorLog
where errorDate = '20081220'

其次，你需要知道SQL Server 2005 DATETIME类型始终包含时间。如果你只检查日期部分的完全匹配，你将仅得到与时间为0:00:00匹配的行 - 没有其他内容。

你可以使用推荐的任何范围查询之一，或在SQL Server 2008中使用DATE日期时间 - 或者你可以执行类似于以下的检查：

select * from tblErrorLog
where DAY(errorDate) = 20 AND MONTH(errorDate) = 12 AND YEAR(errorDate) = 2008

哪种方法对您来说最好，就使用哪种。

如果您需要经常执行此查询，您可以尝试规范化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

然后你可以更轻松地查询：

select * from tblErrorLog
where ErrorMonth = 5 AND ErrorYear = 2009

等等，因为这些字段是计算和持续的，所以它们始终是最新和最新的，并且由于它们是持久化的，如果需要，甚至可以对它们进行索引。

- marc_s

---

十年过去了，你的回答对我帮助很大。非常感谢！ - Mauricio Crispim

你在2023年还会推荐这种日期时间格式吗？我从来没有听说过或见过没有分隔符保存的情况。尤其是对于每年超过1百万条数据的大型数据集。 - veritaS

@veritaS：是的！ 绝对没错，如果您仍在使用 DATETIME 数据类型，这仍然有效。如果您使用的是较新的数据类型，如 DATE 或 DATETIME2(n)，它们对格式要求不那么严格，您绝对可以使用“-”作为分隔符。 - marc_s

Answer 3

你没有说明你使用的是哪个数据库，但在MS SQL Server中，可以这样实现：

WHERE DateField = {d '2008-12-20'}

如果它是一个时间戳字段，那么你需要一个范围：

WHERE DateField BETWEEN {ts '2008-12-20 00:00:00'} AND {ts '2008-12-20 23:59:59'}

- J__

---

在 MS SQL Server 2005 中，它不像描述/要求的那样工作，我刚尝试了一下。令人惊讶的是，它是有效的，它只会给出日期为上述值且时间为00:00:00的结果。 - Meff

谢谢，原始问题似乎只是一个没有时间的日期字段。已经为日期时间字段添加了范围。 - J__

@Meff 这个语法适用于支持ODBC标量函数的任何数据库，而不仅仅是MS SQL。然而，它所做的只是将常量的类型从字符串转换为日期。 - Keith

Answer 4

假设我们讨论的是SQL Server DateTime。

注意：BETWEEN 包括范围的两个端点，因此从技术上讲，以下模式是错误的：

errorDate BETWEEN '12/20/2008' AND '12/21/2008'

我对于这样的时间范围，偏好的方法是：

'20081220' <= errorDate AND errordate < '20081221'

可以处理常见的索引（范围扫描、可搜索谓词、无函数）并正确截断下一天的午夜时间，而不依赖于SQL Server的时间粒度（例如23:59:59.997）。

- onupdatecascade

---

我似乎在这个问题上得到了几个负评，我不知道为什么。如果你认为这个解决方案不可行，请指教！ - onupdatecascade

我刚刚在最初发布后的某个时间查看了这个问题，并注意到它没有包括时间，同时也注意到OP也没有提到，但你提到了SQL粒度。我还是点了赞，因为我也使用这种格式。 - htm11h

Answer 5

使用转换函数获取特定日期的所有条目。

Select * from tblErrorLog where convert(date,errorDate,101) = '12/20/2008'

请参阅CAST和CONVERT以获取更多信息。

- Middletone

---

这不是一个好的做法，因为如果对列使用了转换，就无法使用索引。 - James Z

Answer 6

select * from tblErrorLog
where errorDate BETWEEN '12/20/2008' AND DATEADD(DAY, 1, '12/20/2008')

- Meff

Answer 7

请使用以下查询以获得更清晰的理解。

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
  FROM [dbo].[PublishedInfo]
  where PublishedDate >= '2022-02-14T11:31:16.5299166+00:00'

- logeshpalani31