SQL Server 2008 has introduced a few new data types, among others the new date types, like date, time, datatime2 and datetimespan. Because .Net 2 was released before SQL Server 2008 has introduced these data types, there are no classes that map to these new types in .Net 2.

But this has changed with .Net 2 SP1, which introduces the DateTimeOffset structure.

So, what should we expect when we run an application without SP1?
When we connect to SQL Server 2008 using .Net 2, the version of the runtime of the connecting application will determine the .Net data type that is returned to the user. For example, for a SQL datetimespan column without  .Net SP1 we get back a string, with SP1 we get a DateTimeOffset. Note that this happens even if you compile your application on a machine with SP1. So even though you have compiled and tested your application on .Net 2 SP1, a customer who runs the client without SP1 may experience a different behaviour. So what are these differences:

We will use a simple table that contains the new date data types:

CREATE TABLE someProblematicTypes
    (
      c_date date
    , c_time time
    , c_datatime2 datetime2
    , c_datetimeoffset datetimeoffset
    )

Insert a single row:

INSERT  INTO someProblematicTypes
VALUES  (
          GETDATE()
        , GETDATE()
        , GETDATE()
        , GETDATE() )

Then we create a C# application that uses an SqlDataReader  to retrieve the information about the data stored in this table. We compile this on a machine with .Net2 SP1, and run it on two machines, one with .Net 2 SP1 and one without. The instance of the SqlDataReader returns an object for the various columns, and the values and their types for the above table are:

Without SP1:

Column c_date
   Value:2008-06-20
   Type :System.String
Column c_time
   Value:2008-06-20
   Type :System.String
Column c_datatime2
   Value:2008-06-20 12:11:28.6000000
   Type :System.String
Column c_datetimeoffset
   Value:2008-06-20 12:11:28.6000000 +00:00
   Type :System.String

With SP1:

Column c_date
   Value:20/06/2008 00:00:00
   Type :System.DateTime
Column c_time
   Value:20/06/2008 00:00:00
   Type :System.DateTime
Column c_datatime2
   Value:20/06/2008 12:11:28
   Type :System.DateTime
Column c_datetimeoffset
   Value:20/06/2008 12:11:28 +00:00
   Type :System.DateTimeOffset

That the results are different, and let’s not forget that it is the same executable that is being run. Without .Net2 SP1 we are getting strings instead of DateTime and the new DateTimeOffset. This could upset the application, and if our application was expecting a DateTime type, and got a string, we could easily get an invalid cast exception.

Also, we must be careful when we use the new DateTimeOffset type in our code. The application that is compiled and tested on a machine with SP1 will by default start on a client machine that does not have .Net 2 SP1. However, when it gets to the new data type, it will throw an unknown type exception. Unfortunately, since .Net 2 SP1 is relatively new (released November 2007), many have not upgraded to it. There are two general solutions to this. Either force the your customers to upgrade to .Net 2 SP1, or your application must be prepared for the above type differences and must be tested both with and without .Net 2 SP1.