As title says, I've got table data of contract end dates and I need to return a list of all rows where the end date is between 15 and 20 days away from now. I have the following code:
正如标题所说，我有合同结束日期的表数据，我需要返回结束日期在15到20天之间的所有行的列表。我有以下代码：
WHERE (DATEDIFF([day], Contract.EndDate, GETDATE()) < 20)
AND (Contract.EndDate < DATEADD([day], 15, GETDATE()))
2 个解决方案
#1
2
Your query will return the rows whose contract.endate less than 20 days from now. Try this where clause
您的查询将返回其contract.endate从现在起不到20天的行。试试这个where子句
DATEDIFF([day], Contract.EndDate, GETDATE()) between 15 and 20
#2
2
This is much better written as:
这写得好得多：
WHERE Contract.EndDate >= DATEADD(DAY, 15, GETDATE())
AND Contract.EndDate < DATEADD(DAY, 21, GETDATE())
N.B I have assumed that you want to include things from the 20th day, so have added 21 days to GETDATE(), if this is an incorrect assumption then just change it to 20
N.B我假设您想要包含第20天的内容，所以在GETDATE（）中添加了21天，如果这是一个不正确的假设，那么只需将其更改为20
Doing it this way means the two constants (15 and 21 days ahead) are calculated once at run time, then no other functions are used, whereas if you use something like:
这样做意味着两个常量（提前15天和21天）在运行时计算一次，然后没有使用其他函数，而如果你使用类似的东西：
DATEDIFF([day], Contract.EndDate, GETDATE()) < 20
You have to perform the DATEDIFF function on every row, this means that any index that exists on Contract.EndDate cannot be used (the predicate is not sargable). Even if no index exists, despite being very little overhead, the DATEDIFF function still has a tiny bit, so why perform a function hundreds, thousands, maybe millions of times when once will suffice.
您必须在每一行上执行DATEDIFF函数，这意味着不能使用Contract.EndDate上存在的任何索引（谓词不可搜索）。即使没有索引存在，尽管开销很小，但DATEDIFF函数仍然有一点点，所以为什么只要一次就能执行数百，数千甚至数百万次的函数。
It is also mostly personal preference, because as long as you understand what BETWEEN is doing queries can be written correctly, but I always use an open ended range when working with dates (i.e DATE >= X AND DATE < Y). It is much clearer (IMO) and much less likely to cause unexpected results. Aaron Bertrand has written a very good article on the subject - What do BETWEEN and the devil have in common?
它也主要是个人偏好，因为只要您了解BETWEEN正在进行的查询可以正确编写，但在处理日期时我总是使用开放式范围（即DATE> = X AND DATE
For what it is worth, the problem with your query is your two clauses are both specifying the date is less than a certain date:
对于它的价值，你的查询的问题是你的两个条款都指定日期小于特定日期：
WHERE (DATEDIFF([day], Contract.EndDate, GETDATE()) < 20)
This will limit to rows where "EndDate" is less than 20 days in the future, which is desired behaviour, and the next clause
这将限制“EndDate”在将来少于20天的行，这是期望的行为，以及下一个子句
AND (Contract.EndDate < DATEADD([day], 15, GETDATE()))
Will limit the rows to where the "EndDate" is less than 15 days in the future, which is not desired behaviour, the < in this second predicate should be >.
将行限制为“EndDate”未来不到15天的位置，这不是所希望的行为， <在第二个谓词中应该是> 。
Using today as an example you essentially have a where clause that states
以今天为例，你基本上有一个where子句
WHERE Contract.EndDate < '2015-08-18'
AND Contract.EndDate < '2015-08-13'
So you have the right upper limit, but this will also return all rows in the past.
所以你有正确的上限，但这也将返回过去的所有行。
#1
2
Your query will return the rows whose contract.endate less than 20 days from now. Try this where clause
您的查询将返回其contract.endate从现在起不到20天的行。试试这个where子句
DATEDIFF([day], Contract.EndDate, GETDATE()) between 15 and 20
#2
2
This is much better written as:
这写得好得多：
WHERE Contract.EndDate >= DATEADD(DAY, 15, GETDATE())
AND Contract.EndDate < DATEADD(DAY, 21, GETDATE())
N.B I have assumed that you want to include things from the 20th day, so have added 21 days to GETDATE(), if this is an incorrect assumption then just change it to 20
N.B我假设您想要包含第20天的内容，所以在GETDATE（）中添加了21天，如果这是一个不正确的假设，那么只需将其更改为20
Doing it this way means the two constants (15 and 21 days ahead) are calculated once at run time, then no other functions are used, whereas if you use something like:
这样做意味着两个常量（提前15天和21天）在运行时计算一次，然后没有使用其他函数，而如果你使用类似的东西：
DATEDIFF([day], Contract.EndDate, GETDATE()) < 20
You have to perform the DATEDIFF function on every row, this means that any index that exists on Contract.EndDate cannot be used (the predicate is not sargable). Even if no index exists, despite being very little overhead, the DATEDIFF function still has a tiny bit, so why perform a function hundreds, thousands, maybe millions of times when once will suffice.
您必须在每一行上执行DATEDIFF函数，这意味着不能使用Contract.EndDate上存在的任何索引（谓词不可搜索）。即使没有索引存在，尽管开销很小，但DATEDIFF函数仍然有一点点，所以为什么只要一次就能执行数百，数千甚至数百万次的函数。
It is also mostly personal preference, because as long as you understand what BETWEEN is doing queries can be written correctly, but I always use an open ended range when working with dates (i.e DATE >= X AND DATE < Y). It is much clearer (IMO) and much less likely to cause unexpected results. Aaron Bertrand has written a very good article on the subject - What do BETWEEN and the devil have in common?
它也主要是个人偏好，因为只要您了解BETWEEN正在进行的查询可以正确编写，但在处理日期时我总是使用开放式范围（即DATE> = X AND DATE
For what it is worth, the problem with your query is your two clauses are both specifying the date is less than a certain date:
对于它的价值，你的查询的问题是你的两个条款都指定日期小于特定日期：
WHERE (DATEDIFF([day], Contract.EndDate, GETDATE()) < 20)
This will limit to rows where "EndDate" is less than 20 days in the future, which is desired behaviour, and the next clause
这将限制“EndDate”在将来少于20天的行，这是期望的行为，以及下一个子句
AND (Contract.EndDate < DATEADD([day], 15, GETDATE()))
Will limit the rows to where the "EndDate" is less than 15 days in the future, which is not desired behaviour, the < in this second predicate should be >.
将行限制为“EndDate”未来不到15天的位置，这不是所希望的行为， <在第二个谓词中应该是> 。
Using today as an example you essentially have a where clause that states
以今天为例，你基本上有一个where子句
WHERE Contract.EndDate < '2015-08-18'
AND Contract.EndDate < '2015-08-13'
So you have the right upper limit, but this will also return all rows in the past.
所以你有正确的上限，但这也将返回过去的所有行。