CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Registered User
Registered User

Reputation: 1564

Find orders placed on last day of any month using sargable query?

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?

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

Upvotes: 1

Views: 2213

Answers (6)

Julius V. Sudds
Julius V. Sudds

Reputation: 1

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

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

Upvotes: 0

Maverick
Maverick

Reputation: 1185

You can also do is:

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

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460238

DATEADD is 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".

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

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.

Upvotes: 2

Punter015
Punter015

Reputation: 1796

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:

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

Upvotes: 0

TTeeple
TTeeple

Reputation: 2989

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.

Upvotes: 0

Related Questions