In SQL Server 2000 and 2005 there is no concept of a DATE data type, only a DATETIME. So, how do you get the DATE element only, i.e. with a 00:00 time element. One of my colleagues needed to do this, so after a bit of google searching he discovered the following.

-- Get today's date without the time element
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))

I would probably have done it via a CONVERT/CAST operation, converting to a VARCHAR and then back to a DATETIME, but this is a much more efficient method.

In SQL Server 2008 there is a DATE data type so this will no longer be an issue.