SQL Server 2008 is upon us and if there is one feature about which I am somewhat excited (sad geek, I know) is the plethora of new data types designed purely to store dates and times. To explain the reasons for this excitement let me just list the problems with existing SQL types.
The first major issue is that of resolution: Smalldatetime has resolution of one minute which makes it pretty much useless if you want to store in it anything but the date. The Datetime on the other hand offers a fixed resolution of 0.00333 sec which seems to be more than enough for most applications. However it is not the fact the resolution is too high or too low, it is the fact that it is fixed which is the problem. Such relatively high resolution is not required by most apps and because it is given by default, whether we like it or not, leads us directly to problem no two.
The second issue is that of size: 8 bytes long Datetime is an awful lot of space especially when used as a partitioning field of a FACT table in a data warehouse with hundreds of millions of rows. If you think that these days size of the data type is not that important think twice: why do you think Microsoft introduced VARDECIMAL in SP2 for SQL Server 2005?
The third major issue is that of range: the range of values that can be stored in a Datetime column does not match the range of .NET’s System.DateTime. Because the minimum value for Datetime supported in SQL 2005 (01/01/1753) is different from System.DateTime.MinValue (01/01/0001) as soon as you try to hand the latter to SQL Server, the program blows up nicely.
The fourth problem is related to storage of both date and time in the same column. What if someone wants to see all the transactions that took place after 06PM? The only way to achieve this is to use the DATEPART function to extract the relevant portion of the field and this immediately rules out any index usage (lets not get into calculated columns and function based indexes at this point). The end result is that although the query may return only a fraction of all the rows we’re still looking at a full table scan. Now let’s look at the problem of filtering by date: if we want to extract all the rows where date is equal to 13/11/2007 what we really say is 13/11/2007 00:00:00. This means that querying for events that took place on a particular day becomes awkward exercise along the lines of Transaction_Time between ‘13/11/2007 00:00:00’ and ‘13/11/2007 23:59:59’. The common approach to solving this problem is to use two separate fields to store date and time, this however leads to increased storage requirements.
SQL Server 2008 finally offers an elegant answer to all of these problems: there are separate Date and Time data types and to make things even better the Time has varying "precision". Both of them are tiny (3 bytes for the Date and 3-5 bytes for the Time depending on precision) so we can store date and time separately in just 6 bytes (assuming time resolution of 1 sec).
To make things even better there is now a “proper” Datetime2 data type which is perfectly “aligned” with .NET’s System.DateTime. This alignment is important for the reasons I've already mentioned: loss of precision and problems when trying to query/insert/update data using DateTime.MinValue are two major examples.
And last but not least the there is a Datetimeoffset data type which is identical in range and resolution to Datetime2 but stores the time zone information as well. This comes very handy when data stored in central database is accessed from different locations (countries). In such a case users across various time zones will be able to see the values stored in the database converted to their local times: 12:00 in London is 13:00 in Warsaw etc.
I'd love to say that people from SQL Server team did an excellent job as they truly went an extra mile to solve all of the date and time problems, however as soon as I put all those new data types to test I discovered some teething issues. New data types work as expected with ADO .NET when using Dataset or IDataReader: the values returned are sensibly mapped to .NET types (DateTime, TimeSpan or DateTimeOffset). My biggest issue however is that some of them do not work with LINQ to SQL, or at least I was not able to make them work. When mapping Time to either TimeSpan or DateTime, InvalidCastException gets thrown. Things get even more interesting when mapping Datetimeoffset column to a field of equivalent .NET type: all you get is System.Security.VerificationException with a mesage that says: "Operation could destabilize the runtime".
The only way (with which I was able to come up so far) to overcome the Time mapping issue is to pass varchars/strings instead of Time as arguments to stored procedures and merge Times with Dates on the way back to produce a Datetime. In spite of all these issues in an "airline project" on which I'm working right now, finally having separate data types and columns for dates and times is a godsend.