I thought I've blogged this tip before, but I saw someone else mention it on a mailing list this morning. If you ever want to remove the time portion in a MSSQL Server, you can do this by removing the decimal portion of the date/time stamp.
SQL Server stores date/times as a numeric value where the integer portion of the value represents the number of days since epoch and the time is represented by the decimal portion of the value. This allows us to cast a datetime field as a float and then round down to the nearest integer:
We first convert the datetime to a float:
We next use the floor() function to round down to the nearest integer:
Finally, we convert the value back to a datetime object:
This allows us to obtain all the records created by on the current date by doing something like:
Since the above code does require an operation be done on each CreateDate record, depending on your schema and version of SQL Server, you may get better results by using:
In SQL Server 2005 the execution plan for the two queries are equal, so they should be interchangeable.