选择日期等于今天日期减去1天的每个日期 - 在月/年不使用dateadd - T SQL

Like the title says, I am trying to build a query that selects all records from a database table where the date is equal to yesterdays date.

就像标题所说，我正在尝试构建一个查询，从日期等于昨天日期的数据库表中选择所有记录。

The date column in the table is however of the format datetime (with hours, minutes, seconds as well) so I do the select based on the dates year, month and day (times don't matter as long as the date is yesterday).

然而，表中的日期列的格式为datetime（也包括小时，分钟，秒），因此我根据日期年，月和日进行选择（只要日期是昨天，时间无关紧要）。

To achieve this I have build the following query:

为此，我构建了以下查询：

SELECT 
    * 
FROM
    qryTouchBoekingen 
WHERE 
    (DATEPART(yyyy, myDateTime) = DATEADD(dd, -1, Datepart(dd, GetDate())) 
    AND (DATEPART(mm, myDateTime) = DATEADD(dd, -1, Datepart(mm, GetDate())) 
    AND (DATEPART(dd, myDateTime) = DATEADD(dd, -1, Datepart(dd, GetDate())) )
ORDER BY
    Starttijd ASC

Though this doesn't return any records. When I only use it on the day part of the myDateTime column then it works (but obviously also returns all other years and months with that specific date).

虽然这不会返回任何记录。当我只在myDateTime列的那一天使用它时，它可以工作（但显然也会返回该特定日期的所有其他年份和月份）。

I also couldn't do it using:

我也无法使用：

SELECT 
    *
FROM
    qryTouchBoekingen 
WHERE 
    myDateTime = DATEADD(dd, -1, GetDate())

because this give errors on the time.

因为这会给时间带来错误。

3 个解决方案

#1

How about this much simpler version:

这个更简单的版本怎么样：

cast(myDateTime as date) = cast(dateadd(day, -1, getdate()) as date)

Or, even better:

或者，甚至更好：

(myDateTime >=  cast(dateadd(day, -1, getdate()) as date) and
 myDateTime < cast(getdate() as date)
)

This version is more explicit in its ability to take advantage of an index. (The first will also take advantage of an index on myDateTime, but that is an exception to the rule that functions preclude the use of indexes.)

该版本在利用索引方面更加明确。（第一个也将利用myDateTime上的索引，但这是函数排除索引使用的规则的一个例外。）

#2

Try this one. You should count previous day before making datepart from it

试试这个。你应该在前一天计算它之前的日期部分

Select * from qryTouchBoekingen 
WHERE ( DATEPART(yyyy, myDateTime) = Datepart(dd, DATEADD(dd, -1, GetDate())) 
AND (DATEPART(mm, myDateTime) = Datepart(mm, DATEADD(dd, -1, GetDate())) 
AND (DATEPART(dd, myDateTime) = Datepart(dd, DATEADD(dd, -1, GetDate())) )
Order by Starttijd ASC

#3

DOes this work:

做这个工作：

Select 
    * 
from 
    qryTouchBoekingen 
WHERE 
    CAST( myDateTime AS DATE) = CAST(DATEADD(day, -1, GetDate()) AS DATE)
Order by 
    Starttijd ASC

#1