I am not quite following what you are asking. The query I posted earlier will do exactly what you asked for, namely, include all the values in the [Date] column that start at midnight yesterday, and end before the beginning of today.
Here I am making the assumption that the data type of your [Date] column is DATETIME or one of the other datetime data types. Use the code that I posted and see if you get the results you want. If you are not getting the results you are looking for, we can figure out why.
If you are getting the results you want, and you want more clarifications on why what I posted is the correct way to do it (rather than generating a string such as '8/05/2015 00:00:00'), we can discuss that as well.