Desconozco si existe la funcion CURRENT_DATE o CURRDATE() en SQL Server...
Pero otra forma seria restarle 1 dia a la fecha de hoy para que los datos mostrados fuesen los de ayer:
SELECT * FROM tabla WHERE fecha = (CURRENT_DATE - 1)
Lo de shellroot tambien es valido que conste.