I have a datetime column in a table with a time component.
In any case, can I set '2011-03-14 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)
Until 2k8 I always:
DATEADD(DAY, DATEDIFF(DAY, 0, datecol), 0)
, "" , :
SELECT DateAdd(Day, 0, DateDiff(Day, 0, GetDate()))
/ , / , DateTime/SmallDateTime.
SqlServerCentral.com, , .
UPDATE: Gail Shaw posted a good comparison of the performance of conventional methods (this one is apparently the fastest, but only a small amount): http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date- truncations /
An alternative that does the same, depending on where you use it. It could also be used in a view, etc., where a full script is not possible.
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))