Query to fetch the Yesterday's Data

Seems to be easy question. And many of you may be already knew 2-3 ways of doing it. Here is the query I use normally to get the all the data from the previous day(Yesterday).

SELECT Id, OrderId, CustomerId FROM Orders WHERE Date >= DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) AND Date < DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0)

Make it more readable and scalable using variable

DECLARE @YesterDay DATETIME, @Today DATETIME SET @YesterDay = DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) SET @Today = DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0) SELECT Id, OrderId, CustomerId FROM Orders WHERE  Date >= @YesterDay AND Date < @Today

Logic : Basically above WHERE conditions is like - Greater than Equal to Yesterday and Less than Today.

This DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) gives Yesterday's date with time as 00:00:00.

And this DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0)  gives today's date with time as 00:00:00.

You can do a quick check by executing -

SELECT DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0) AS YDay ,DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0) as ToDay

Reason for doing this way instead of using some obvious ones – As you may heard this from many SQL experts, “Whenever possible never use CONVERT/CAST or any functions in WHERE clause on table's column.”

Question is Why?

Well whenever you use the any function on a column , SQL Server no longer uses the Index define on particular column. And use of a CAST or CONVERT functions slows down the query execution considerably.

You may say, I used the DateAdd and DateDiff function in the WHERE clause. Yes, I used them, but NOT on Date column from the table. I used them with the Getdate function which is more of a constant and NOT part of the table's column.

Query Optimization Tip :

Whenever possible never use CONVERT/CAST or any functions in WHERE clause on table's column.

In future I'll have some more posts on Date comparison in WHERE condition. Also some more simple tricks to optimized the everyday's queries.

 

Mangal Pardeshi
SQL MVP