Date Time in SQL Server 2005

I have a datetime column in table that has time component.

Is there anyway I can set '2011-03-14开发者_JS百科 11:46:31' to '2011-03-14 00:00:00'?

declare @date as datetime

set @date = getdate()

Select  Cast(Floor(Cast(@date as float)) as DateTime)

Prior to 2k8 I always;

DATEADD(DAY, DATEDIFF(DAY, 0, datecol), 0) 

As all the previous answers are the same, I'll add the other "good" option I know of:

SELECT DateAdd(Day, 0, DateDiff(Day, 0, GetDate()))

This is preferred by some/many people because it doesn't rely on the undocumented/unsupported fact that days are represented by integers in DateTime/SmallDateTime.

There have been interesting discussions on this topic in the SqlServerCentral.com forums, but I couldn't quickly find a link, sorry.

UPDATE: Gail Shaw posted a nice quick performance comparison of the common methods (this one apparently is fastest, but only by a v small amount): http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/

An Alternative that does the same thing, depending where you use it. This would also be possible to use in a view etc where a full script isn't possible.

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))