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()