Save DateTime in mssql 2005 without hours, minutes and seconds

Question

I want to save my dates in sql2005 as dates (without hour minutes and seconds). I want to do this because the between function isn't always correct if the hours, minutes and seconds are filled in.

but neither datetime nor smalldatetime allows this, in 2008 you have the Date column which can do this.

I've also found this question which helps me out but I don't like the casting to execute my queries: How to remove the time portion of a datetime value (SQL Server)?

Answer 1

The non-casting way:

SET @d = DATEADD(dd, 0, DATEDIFF(dd, 0, @dt))

This adds the number of days between 0 and your date to 0, resulting in the time-part to be zero.

If that is faster than the explicit CAST approach must be tested. The use of 0 suggests some implicit casting will be done in this expression.