In 2008, the best way to truncate hours is CONVERT(date, YourDate). Took long enough to get some new time datatypes. hah!

Also, since the data warehouse I work with is using day as the granularity, we often need to use something like this:

AND CONVERT(date, TransactionDate) = DWDate

Although if performance is an issue, I guess we could do it like this:

AND TransactionDate >= Table1.DimTimeDate

AND TransactionDate < Table2.DimTimeDate --(date + 1d)

Totally removing any functions would require a second join on the time dimension at DATEADD(dd,1,DWDate).