CODEWITHSUNDEEP
sql-serverreporting-servicesreporting
CJ Boat
CJ Boat

Reputation: 71

Select all rows from the previous date

I am trying to make a SQL query (to be converted into a SSRS report) that will grab everything opened in the previous day. This will have to be ran every day, automatically, so entering in the date manually will not be an option really.

Upvotes: 0

Views: 99

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Safest (in terms of changes to, or implicit conversion of, underlying data types) and most efficient (in terms of best chance at using an index to seek) is an open-ended range:

WHERE datetime_column >= DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()),0)
  AND datetime_column <  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()),0);

For some details, see:

Upvotes: 5

M.Ali
M.Ali

Reputation: 69524

WHERE CAST(DATE_Column AS DATE) = CAST(DATEADD(DAY, - 1, GETDATE()) AS DATE)

Upvotes: 1

Sparky
Sparky

Reputation: 15075

select DATEADD(DD,-1,getDate()) as Yesterday

select * from <table> where date_in_question = DATEADD(DD,-1,getDate())

If you only need the date, do this

declare @yesterday as Date

set @yesterday = cast(DATEADD(DD,-1,getDate()) as Date)

select * from <table> where date_in_question = @yesterday

Upvotes: 0

Related Questions