使用sargable查询查找任何月份最后一天的订单？

I wrote this query to find orders placed on last day of any month.

我写了这个查询来查找任何月份最后一天的订单。

I know this approach is not recommended if orderdate is indexed. What approach should i use to make it sargable?

我知道如果orderdate被编入索引,则不建议采用这种方法。我应该采用什么方法来使其成为可靠的?

select o.orderid, o.orderdate, o.custid, o.empid
from  sales.Orders o
where day(o.orderdate) in (30, 31) 
         or (month(o.orderdate) = 02 and day(o.orderdate)= 28) 
         or (month(o.orderdate) = 02 and day(o.orderdate)= 29);

6 个解决方案

#1

You can do this with computed columns:

您可以使用计算列执行此操作:

alter table Orders add column nextdayofmonth as day(dateadd(day, 1, orderdate));

create index orders_nextdayofmonth on orders(orders_nextdayofmonth);

The nextdayofmonth is for the next day, so leap years can easily be handled. After all, the day after the "last day" is the "first day" of the next month.

第二天是第二天,所以闰年很容易处理。毕竟,“最后一天”之后的第二天是下个月的“第一天”。

Then phrase your query as:

然后将您的查询短语为:

where next_dayofmonth = 1

This expression is sargable.

这个表达是可以理解的。

#2

DATEADD is sargable:

DATEADD是sargable:

WHERE DATEADD(day, DATEDIFF(day, 0, o.orderdate), 0) = 
      DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, o.orderdate) + 1, 0))

The first is just the old way to truncate the time from a datetime. The second adds one month, "truncates" the month and subtracts a day.

第一种是从日期时间截断时间的旧方法。第二个增加一个月,“截断”月份并减去一天。

Here's a fiddle that returns the last day of the current month with the same "trick".

这是一个小提琴,它以同样的“技巧”返回当月的最后一天。

#3

This query would fail for leap years as it would give you 2 dates as the last day of the month in February. To make it SARGable, you would need to take out the functions on the date column.

此查询将在闰年中失败,因为它将为您提供2个日期作为2月份的最后一天。要使其成为SARGable,您需要取出日期列中的函数。

#4

This should work fine

这应该工作正常

select o.orderid, o.orderdate, o.custid, o.empid
from  sales.Orders o
where
day(o.orderdate)=day(DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0,o.orderdate) + 1, 0)))

The below query gives the last day of current month, replace getdate() with the date variable as shown above:

下面的查询给出了当前月份的最后一天,将getdate()替换为日期变量,如上所示:

SELECT day(DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)))

#5

You can also do is:

你也可以这样做:

select TOP 1 orderid, orderdate, custid, empid
from  sales.Orders 
ORDER BY orderdate DESC

#6

Get all dates that DAY part of its tomorrow is 1:

获取明天DAY部分为1的所有日期:

SELECT *
FROM Sales.Orders
WHERE DAY(DATEADD(dd, 1, orderdate)) = 1

#1