CODEWITHSUNDEEP
sqlsql-servert-sql
Vicky
Vicky

Reputation: 744

SQL Query to provide data from yesterday between and including 12:00 AM to 11:59 PM

I have a query with the following condition to provide the data from yesterday between and including the time 12:00 AM to 11:59 PM

Select * from table where passDate >= DATEADD(DAY, -1, convert(date, GETDATE()))
and passDate < convert(date, GETDATE())

passDate is a dateTime column.

Will the above yield required result? Is there any better way to do this apart from above?

Upvotes: 1

Views: 5342

Answers (4)

cloudsafe
cloudsafe

Reputation: 2506

Surprised that this method on Brent Ozar uses the index while breaking the rules of sargability, as I understood them:

https://www.brentozar.com/archive/2016/12/date-math-clause/

This method should produce the best results if an index is used: 

Select * 
from table 
where convert(date, [passDate]) = dateadd(d,-1, convert(date, getdate()))

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37500

This will give you desired results:

declare @start datetime = cast(cast(dateadd(day, -1, getdate()) as date) as datetime)
declare @end datetime = dateadd(hour, 12, @start)
--to see, that these are correct boundaries
--select @start, @end
-- today it would return 
-- 2018-05-20 00:00:00.000 | 2018-05-20 12:00:00.000

select * from TBL
where passDate between @start and @end

Upvotes: 0

user1710989
user1710989

Reputation: 117

DECLARE @MidNightToday datetime;
DECLARE @MidNightYesterday datetime;

SET @MidNightToday = Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()));
SET @MidNightYesterday = Convert(DateTime, DATEDIFF(DAY, 1, GETDATE()));

Select * from table where passDate between @MidNightYesterday and 
@MidNightToday;

Upvotes: 0

pacreely
pacreely

Reputation: 1931

If you are unable to remove GETDATE() from your WHERE clause, this is probably a more easy to read solution.

Select 
* 
from 
table 
where 
DATEDIFF(DD,passdate,GETDATE()) = 1

But it isn't the most efficient solution.

It's ideal to pre-load GETDATE() into a variable

DECLARE @d DATE = GETDATE();

Select 
* 
from 
table 
where 
DATEDIFF(DD,passdate,@d) = 1

If there is an index on [passdate] then ideally you should try and avoid Functions in your WHERE clause because it affects SQL Servers ability to use indexes.

Here's something a bit more index friendly (but will need tweaking for your environment), but to do it properly the variables should be the dame data type as the [passdate] column to avoid implicit conversions.

DECLARE @start DATE = DATEADD(DD,-1,GETDATE());
DECLARE @end DATE = GETDATE();

Select 
* 
from 
table 
where 
passdate >= @start
AND
passdate < @end

Upvotes: 2

Related Questions