Jeff Moden (4/7/2011)
Either of the following is what I consider to be a "best practice". They're both very fast but the first one edges out the second when it comes to millions of rows if you need to apply it to something that large.
SELECT CAST(DATEDIFF(dd,0,GETDATE()) AS DATETIME),DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
Replace the GETDATE() with whatever date you may have including dates in a column from a table. Just don't forget to add the FROM clause for the table. 😀
Ah... I just had a horrible thought based on how the original question was posed. If you're trying to use such a thing as criteria for a JOIN or a WHERE clause, DO NOT use either of the two methods above on the COLUMN of a TABLE. Instead, do this...
SELECT something
FROM SomeTable
WHERE SomeDateColumn >= SomeWholeDate
AND SomeDateColumn < DATEADD(dd,1,SomeWholeDate)
The code above finds all the rows in SomeTable that have a date (with or without time) that falls on SomeWholeDate and it does so while still allowing an index on SomeDateColumn to be used. Using just about any formula on SomeDateColumn itself will prevent the use of an index.
--Jeff Moden
Change is inevitable... Change for the better is not.