DATE and TIME data types in SQL Server 2008

June 11, 2007

Sick of using DATETIME to store values which are always going to be the date-part only? Yeah, me too. This is why SQL Server 2008 has new data types called DATE and TIME, which store just the DATE or TIME component of a DATETIME, respectively. Half the size, and much more appropriate if that’s all you need.

So now you can picture code like:

where cast(thedatetime as date) = ‘2007-06-11’

But please be aware. This kind of query is likely to cause a table scan, just as if you cast a FLOAT to an INT. In this scenario, it would be better to use code like:

where thedatetime >= ‘2007-06-11’ and thedatetime < ‘2007-06-12’

…so that an index on thedatetime can be used. But of course this is the code you would’ve used in SQL Server 2005 anyway.

So what’s the point? Well, if you’re dealing with DATETIME fields, I would still be using them in a DATETIME way. But there will be plenty of data models that will start using the DATE type instead, if that’s what’s most appropriate to use.

NOTE: These data types are not available in the June 2007 CTP of SQL Server 2008

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.


Related Blogs