Getting just the date from Sql Server's datetime

Whilst many still advocate using Convert() to drop the time-bit from a datetime[1], going off to a string like that is nothing like as efficient as the numerical alternative:

CAST( FLOOR( CAST( @dateTime AS FLOAT ) ) AS DATETIME )

Sql stores the days since 1900 in the first 4 bytes, and the time in the last 4 bytes. Throwing away the bit after the decimal place (what the above does) just strips those last 4 bytes right how.

But... why AS FLOAT? Why convert straight to an INT?

CAST( CAST( getdate() AS INT ) as Datetime)

Turns out this gives us tomorrow's date! I guess this is because the starting date is actually Jan 1st 1900, and not Jan 0, which could be a classic off-by-one error if you weren't awake.


[Update]: Because casting direct to an int rounds up in the afternoon! Doh! Doh! Doh!. This is exactly why I'm writing this down, because I knew that once and forgot. Anyway, going via a FLOAT is the go, OR the much more legible alternative that Mitch put in the comments : datediff(day, 0, @yourdate)
[/Update]

That being said, if all you want is an integer value that represents a date, just casting datetime to an int seems like a pretty good way to go, provided you never get carried away and cast it back again. Or I guess you could just compensate for the off-by-one and save yourself[1] a lot of pain later on...

If you're really interested, run this and see for your self:

select
 getdate()
 ,FLOOR( CAST( getdate() AS FLOAT )) -- use floating point
 ,datediff(dd, '19000101', getdate()) -- right answer, by definition
 ,Cast(getdate() as int)
 ,CAST( CAST( getdate() AS INT ) as Datetime) -- tomorrow!

[1] Yes, Sql 2008 has a date-only type. But you and I will still be stripping times from datetimes for many, many years to come.
[2] By which I really mean myself, of course