SQL Server：如何从datetime列中选择具有特定日期的记录

I am pretty new to SQL and hope someone here can help me with this:

我对SQL很陌生，希望有人可以帮助我：

I have a table with one column dateX formatted as datetime and containing standard dates. How can I select all records from this table where this dateX equals a certain date, e.g. May 9, 2014 ?

我有一个表格，其中一列dateX格式化为datetime并包含标准日期。如何从此表中选择此dateX等于特定日期的所有记录，例如2014年5月9日？

I tried the following but this returns nothing even if I have several records with this date.

我试过以下但是即使我有这个日期的几条记录也没有返回任何内容。

SELECT     *
FROM         dbo.LogRequests
WHERE     (CONVERT(VARCHAR(10), dateX, 101) = '09/05/14')

Edit: In the database the above example looks as follows, using SQL 2012: 2014-05-09 00:00:00.000

编辑：在数据库中，上面的示例如下所示，使用SQL 2012：2014-05-09 00：00：00.000

Many thanks for any help with this, Mike.

非常感谢Mike的任何帮助。

3 个解决方案

#1

The easiest way is to convert to a date:

最简单的方法是转换为日期：

SELECT *
FROM dbo.LogRequests
WHERE cast(dateX as date) = '2014-05-09';

Often, such expressions preclude the use of an index. However, according to various sources on the web, the above is sargable (meaning it will use an index), such as this and this.

通常，这样的表达式排除了索引的使用。但是，根据网络上的各种来源，上面是可以理解的（意思是它将使用索引），例如this和this。

I would be inclined to use the following, just out of habit:

出于习惯，我倾向于使用以下内容：

SELECT *
FROM dbo.LogRequests
WHERE dateX >= '2014-05-09' and dateX < '2014-05-10';

#2

For Perfect DateTime Match in SQL Server

对于SQL Server中的完美日期时间匹配

SELECT ID FROM [Table Name] WHERE (DateLog between '2017-02-16 **00:00:00.000**' and '2017-12-16 **23:59:00.999**') ORDER BY DateLog DESC

#3

SELECT * FROM LogRequests WHERE cast(dateX as date) between '2014-05-09' and '2014-05-10';

SELECT * FROM LogRequests在'2014-05-09'和'2014-05-10'之间转换WHERE（dateX作为日期）;

#1