SQL statement to select all rows from previous day

To get the "today" value in SQL:

convert(date, GETDATE())

To get "yesterday":

DATEADD(day, -1, convert(date, GETDATE()))

To get "today minus X days": change the -1 into -X.

So for all yesterday's rows, you get:

select * from tablename
   where date >= DATEADD(day, -1, convert(date, GETDATE()))
   and date < convert(date, GETDATE())

get today no time:

SELECT dateadd(day,datediff(day,0,GETDATE()),0)

get yestersday no time:

SELECT dateadd(day,datediff(day,1,GETDATE()),0)

query for all of rows from only yesterday:

select 
    * 
    from yourTable
    WHERE YourDate >= dateadd(day,datediff(day,1,GETDATE()),0)
        AND YourDate < dateadd(day,datediff(day,0,GETDATE()),0)