如何将日期时间与 SQL Server 中的仅日期进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25564482/
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
How to compare datetime with only date in SQL Server
提问by Muhabutti
Select * from [User] U
where U.DateCreated = '2014-02-07'
but in the database the user was created on 2014-02-07 12:30:47.220and when I only put '2014-02-07'
但是在数据库中创建了用户2014-02-07 12:30:47.220并且当我只放'2014-02-07'
It does not show any data
它不显示任何数据
回答by Used_By_Already
DON'T be tempted to do things like this:
不要试图做这样的事情：
Select * from [User] U where convert(varchar(10),U.DateCreated, 120) = '2014-02-07'
This is a better way:
这是一个更好的方法：
Select * from [User] U
where U.DateCreated >= '2014-02-07' and U.DateCreated < dateadd(day,1,'2014-02-07')
see: What does the word “SARGable” really mean?
EDIT + There are 2 fundamental reasons for avoiding use of functions on data in the where clause (or in join conditions).
编辑 + 避免在 where 子句（或连接条件）中对数据使用函数有两个基本原因。
- In most cases using a function on data to filter or join removes the ability of the optimizer to access an index on that field, hence making the query slower (or more "costly")
- The other is, for every row of data involved there is at least one calculation being performed. That could be adding hundreds, thousands or many millions of calculations to the query so that we can compare to a single criteria like
2014-02-07. It is far more efficient to alter the criteria to suit the data instead.
- 在大多数情况下，在数据上使用函数来过滤或连接会消除优化器访问该字段索引的能力，从而使查询变慢（或“成本更高”）
- 另一种是，对于涉及的每一行数据，至少有一个计算正在执行。这可能是向查询添加数百、数千或数百万次计算，以便我们可以与单个条件进行比较，例如
2014-02-07. 相反，更改标准以适应数据要有效得多。
"Amending the criteria to suit the data" is my way of describing "use SARGABLEpredicates"
“修改标准以适应数据”是我描述“使用SARGABLE谓词”的方式
And do not use between either.
并且不要在两者之间使用。
the best practice with date and time ranges is to avoid BETWEEN and to always use the form:
WHERE col >= '20120101' AND col < '20120201' This form works with all types and all precisions, regardless of whether the time part is applicable.
日期和时间范围的最佳做法是避免 BETWEEN 并始终使用以下形式：
WHERE col >= '20120101' AND col < '20120201' 这种形式适用于所有类型和所有精度，无论时间部分是否适用。
http://sqlmag.com/t-sql/t-sql-best-practices-part-2(Itzik Ben-Gan)
http://sqlmag.com/t-sql/t-sql-best-practices-part-2(Itzik Ben-Gan)
回答by Steve Ford
If you are on SQL Server 2008 or later you can use the date datatype:
如果您使用的是 SQL Server 2008 或更高版本，则可以使用日期数据类型：
SELECT *
FROM [User] U
WHERE CAST(U.DateCreated as DATE) = '2014-02-07'
It should be noted that if date column is indexed then this will still utilise the index and is SARGable. This is a special case for dates and datetimes.
应该注意的是，如果日期列被索引，那么这仍然会使用索引并且是 SARGable。这是日期和日期时间的特殊情况。
You can see that SQL Server actually turns this into a > and < clause:
你可以看到 SQL Server 实际上把它变成了一个 > 和 < 子句：
I've just tried this on a large table, with a secondary index on the date column as per @kobik's comments and the index is still used, this is not the case for the examples that use BETWEEN or >= and <:
我刚刚在一个大表上尝试过这个，根据@kobik 的评论，在日期列上有一个二级索引，并且索引仍然被使用，对于使用 BETWEEN 或 >= 和 < 的示例，情况并非如此：
SELECT *
FROM [User] U
WHERE CAST(U.DateCreated as DATE) = '2016-07-05'
回答by imdzeeshan
According to your query
Select * from [User] U where U.DateCreated = '2014-02-07'
根据您的查询
Select * from [User] U where U.DateCreated = '2014-02-07'
SQL Server is comparing exact date and time i.e (comparing 2014-02-07 12:30:47.220with 2014-02-07 00:00:00.000for equality). that's why result of comparison is false
SQL Server 正在比较确切的日期和时间，即（2014-02-07 12:30:47.220与2014-02-07 00:00:00.000相等性比较）。这就是比较结果为假的原因
Therefore, While comparing dates you need to consider time also. You can useSelect * from [User] U where U.DateCreated BETWEEN '2014-02-07' AND '2014-02-08'.
因此，在比较日期时，您还需要考虑时间。您可以使用Select * from [User] U where U.DateCreated BETWEEN '2014-02-07' AND '2014-02-08'.
回答by Bikram
Of-course this is an old thread but to make it complete.
当然，这是一个旧线程，但要使其完整。
From SQL 2008 you can use DATE datatype so you can simply do:
从 SQL 2008 开始，您可以使用 DATE 数据类型，因此您可以简单地执行以下操作：
SELECT CONVERT(DATE,GETDATE())
OR
Select * from [User] U
where CONVERT(DATE,U.DateCreated) = '2014-02-07'
回答by WhiteAngel
You can use LIKEstatement instead of =. But to do this with DateStamp you need to CONVERTit first to VARCHAR:
您可以使用LIKEstatement 而不是=. 但是要使用 DateStamp 执行此操作，您CONVERT首先需要将其设置为 VARCHAR：
SELECT *
FROM [User] U
WHERE CONVERT(VARCHAR, U.DateCreated, 120) LIKE '2014-02-07%'
回答by SnowWhite
Please try this. This query can be used for date comparison
请试试这个。此查询可用于日期比较
select * from [User] U where convert(varchar(10),U.DateCreated, 120) = '2014-02-07'
