Wednesday, September 10, 2008

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

2 comments:

Mitch Wheat said...

I prefer doing it this way: http://mitch-wheat.blogspot.com/2007/05/sql-server-compare-date-part-of.html

piers7 said...

Wouldn't have thought of that. Not only is that a lot easier to remember, but it actually gives the right answer - unlike what I originall wrote above. Opps.

Popular Posts