As I’ve mentioned in other posts, a DateTime is stored using a numerical representation. This provides some interesting usages.
For example this means that in order to add days to a DateTime, one can simply add a number, like this: GetDate() + 5 which will add 5 days to the DateTime returned by GetDate(). Simple and easy.
You can however also convert a DateTime to a float, which provides some other “fun” possibilities.
If you convert a DateTime to a float, you’ll get a decimal number where the portion in front of the decimal point is the Date portion and the number after the decimal point is the Time portion (for 1900-01-01). For example:
DECLARE @DateTime DATETIME
SET @DateTime = '20081224 23:50:10'
SELECT CAST(@DateTime AS float)
This returns 39804.9931712963
If I then execute:
SELECT CAST(39804 AS DATETIME)
I get 2008-12-24 00:00:00.000 meaning the date representation of the DateTime. Subsequently doing it for the remainder portion (0.9931712963) I get 1900-01-01 23:50:10.000
This then means that to get a DateTime cleaned for the time portion all I need to do is:
SELECT CAST(FLOOR(CAST(@DateTime AS FLOAT)) AS DATETIME)
Then I get the Date 2008-12-24 00:00:00.000.
While “fun”, SQL Server 2005 already has a built in conversion so the above will provide you with the same result as doing:
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateTime,105),105)
(the syntax can be read in the MSDN documentation)
I’ve found no difference in the performance between the two when running them, so my thoughts are that SQL Server optimizes them both to identical code to execute.
So which is preferred I guess is a matter of preference and personal opinion.
Generally – I like the “Float” method cause it seems really clean, but it is perhaps more difficult to read and thus maintain further down the line for yourself or others, if one does not know the workings of it.
Code can be too clever as well.