SQLClient Support for the New Date and Time Types in SQL Server 2008


Prior to SQL Server 2008, SQL developers were restricted to using two types to work with date and time values: datetime and smalldatetime. While they are sufficient in most user scenarios, there are some limitations:


1)      Both types include both the date value and time value. There is no way to store only the date or time part. This causes wasted storage and difficulties in computation and indexing.


2)      The range supported of the datetime type does not align with the range of .NET CLR DateTime type.


3)      The datetime precision of .333 seconds is not sufficient for some applications.


4)      The types are not time-zone aware.


To address these limitations, SQL Service 2008 introduced four new date/time data types: date, time, datetime2, datetimeoffset. The first two types are date-only and time-only respectfully. time type supports user-defined fractional second precision. datetime2 combines date and time into a single data type. datetimeoffset has all the features of datetime2 with an additional time zone offset. The range of datetime2 and datetimeoffset are fully aligned with the .net CLR types DateTime and DateTimeOffset (introduced in .NET Framework 3.5).


In .NET Framework 3.5, SQLClient has been extended to support the four new types.


 


Type Version System


To enable the feature, the first step is to explicitly specify the Type Version System to SQL Server 2008 or Latest in the connection string. Otherwise, the client would be treated like a SQL Server 2005 client. Here is the code snippet:


            using (SqlConnection conn = new SqlConnection("Server=localhost;Integrated Security=SSPI;Type System Version=SQL Server 2008"))


            {


                conn.Open();


                //...


            }


 


Scale for time, datetime2, and datetimeoffset


For time, datetime2, and datetimeoffset, users are allowed to define the fractional second precision. It might sound confusing, the correct term for it is scale. With scale = x, the increment would be 1/10X seconds. So you should use the Scale property in SqlParameter to specify the fractional second precision and you look for the Scale column in the schema table returned by SqlDataReader.GetSchemaTable().


 


CLR Type Mapping and Inference


date, datetime2 are mapped to DateTime. time is mapped to TImeSpan. datetimeoffset is mapped to DateTimeOffset. datetime2 and datetimeoffset have the same range as their CLR counterparts.


For SqlParameter, if you use AddWithValue method, DateTime would always be inferred as datetime. To explicit specify the value as datetime2, you need to set the SqlDbType to SqlDbType.DateTime2.


 


Some New SqlClient APIs Related to New Date/Time Types


New SqlDbType enumerations:


SqlDbType.Date


SqlDbType.Time


SqlDbType.DateTime2


SqlDbType.DateTimeOffset


New SqlDataReader methods:


SqlDataReader.GetDateTimeOffset()


SqlDataReader.GetTimeSpan()

Comments (3)

  1. Jim says:

    Thanks for the additions.

    This page http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader_members.aspx needs to be told about GetDateTimeOffset

    What about a method GetSqlDateTimeOffset, it doesn’t seem to be defined in 3.5

  2. MikeDube says:

    Hi Bill,

    I’m trying to populate a data table that contains a datetime2 column using SqlDataAdapter.Fill().  I’ve set the connection string as you direct above, but the column is still populated as a standard datetime column.  While the data in SQL has 7 significant digits of the seconds fraction, when it comes into the data table, everything below seconds is lost (0 milliseconds).  I need to have greater granularity than what I am getting, and I assume I must be doing something wrong.  Any pointers?

    Thanks,

    Mike

Skip to main content