如何将日期时间与 SQL Server 中的仅日期进行比较 [英] How to compare datetime with only date in SQL Server

问题描述

Select * from [User] U其中 U.DateCreated = '2014-02-07'

但在数据库中，用户是在 2014-02-07 12:30:47.220 上创建的，当我只输入 '2014-02-07'

它不显示任何数据

解决方案

不要试图做这样的事情:

Select * from [User] U where convert(varchar(10),U.DateCreated, 120) = '2014-02-07'

这是一个更好的方法:

Select * from [User] U其中 U.DateCreated >='2014-02-07' 和 U.DateCreated <dateadd(day,1,'2014-02-07')

请参阅:SARGable"一词的真正含义是什么?

编辑 +避免在 where 子句(或连接条件)中对数据使用函数有两个根本原因.

1. 在大多数情况下，对数据使用函数来过滤或连接会消除优化器访问该字段索引的能力，从而使查询速度变慢(或成本更高")
2. 另一个是，对于涉及的每一行数据，至少有一个计算正在执行.这可能会向查询添加数百、数千或数百万次计算，以便我们可以与 2014-02-07 等单一条件进行比较.改为更改标准以适应数据要有效得多.

修改标准以适应数据"是我描述使用 SARGABLE 谓词"的方式

<小时>

不要在两者之间使用.

<块引用>

日期和时间范围的最佳做法是避免 BETWEEN 和始终使用以下形式:

WHERE col >= '20120101' AND col <'20120201' 此表格适用于所有类型和所有精度，无论时间部分是否适用.

http://sqlmag.com/t-sql/t-sql-best-practices-part-2 (Itzik Ben-Gan)

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.220 and when I only put '2014-02-07'

It does not show any data

解决方案

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).

1. 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")
2. 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.

"Amending the criteria to suit the data" is my way of describing "use SARGABLE predicates"

---

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.

http://sqlmag.com/t-sql/t-sql-best-practices-part-2 (Itzik Ben-Gan)

这篇关于如何将日期时间与 SQL Server 中的仅日期进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！