Use this code to get today no time:
SELECT dateadd(day,datediff(day,0,GETDATE()),0)
To get yesterday no time, use this query:
SELECT dateadd(day,datediff(day,1,GETDATE()),0)
Query for all of rows from only yesterday try this:
select
*
from yourTable
WHERE YourDate >= dateadd(day,datediff(day,1,GETDATE()),0)
AND YourDate < dateadd(day,datediff(day,0,GETDATE()),0)