New Date and Time types in SQL Server 2008

I’ve been going through some of the new language features in SQL Server 2008.   The easiest to find are the new HierarchyID, sparse data columns, and brand spanking new date and time types.

I’m just going to (briefly) discuss the date and time types.

Start with new Date and Time types.  Not DateTime, or SmallDateTime (does anyone even use this type?).  No, separate Date and Time types.  I have no doubt these will make my life easier for a number of sql functions. 

You don’t have to stretch your imagination far to find a use for the Date.  The Date type can take any date between year 1 and year 9999.  The DateTime can only go back as far as 1753.  Plus it only takes up 3 bytes (I’m not one to worry about things like that very often, but with large databases these things do come into play).

The Time type (no date allowed) is accurate up to 100 nanoseconds.  I say up to because it can be configured to be less accurate — and take up less space (anywhere from 3 to 5 bytes).

Next comes the interesting one: DateTime2.  If you were wondering why we need a new DateTime type, there are two reasons.  DateTime is not SQL standard compliant, and DateTime is not completely compatible with the .Net DateTime type.  SQL compliance isn’t a big concern for me, but better .Net compatibility is good.

So for details.  DateTime2 is basically a combination of the new Date and Time types.  The time portion is configurable again, and there are a lot of string formats that the DateTime2 will accept.  More interesting, the current guidance out of Microsoft for .Net developers is to stop using the DateTime type in favor of DateTime2.  This is mainly for the .Net compatibility. 

So why didn’t they just make DateTime compatible with .Net.  Well, in databases, things just don’t work that way.

Impact: But I still have a few questions about these types that are not answered as of yet.  In .Net we don’t have dedicated Date and Time types, just a DateTime.  I can deal with the Date…but the Time will be interesting.   It will be interesting to see how LINQ to SQL, NHibernate, and SubSonic all handle these changes.

More info can be found on Ravi s. Maniam’s blog.