更新时间:2022-10-18 09:06:34
不要试图做这样的事情:
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')
编辑 +避免在 where 子句(或连接条件)中对数据使用函数有两个根本原因.
2014-02-07
等单一条件进行比较.改为更改标准以适应数据要有效得多.修改标准以适应数据"是我描述使用 SARGABLE
谓词"的方式
不要在两者之间使用.
日期和时间范围的***做法是避免 BETWEEN 和始终使用以下形式:
WHERE col >= '20120101' AND col
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).
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)