I wanted to share this bit of SQL wisdom I picked up a few months ago on the ASP.Net forums (http://forums.asp.net). Let's say you have an orders table and you have an orderDate column that contains the Date & Time of the order.  If you wanted to create a query to select all orders made yesterday at any time, I cameup with this working (if not long winded) method:

 Where orderDate = CAST(MONTH(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(DAY(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(YEAR(DATEADD(day, - 1, GETDATE())) AS varchar))

I was treated to an extreme stream lining of this on the forums.  The resulting query was:

Where orderDate >= convert(Varchar, Getdate() -1, 101) And orderDate < convert(Varchar, Getdate() , 101)