Date and time data

SQL Server 2008 introduced new data types for handling date and time information. The new data types included separate types for date and time, and expanded data types with greater range, precision, and time-zone awareness. Starting with the .NET Framework version 3.5 Service Pack (SP) 1, the .NET Framework Data Provider for SQL Server (System.Data.SqlClient) provides full support for all the new features of the SQL Server 2008 Database Engine. You must install the .NET Framework 3.5 SP1 (or later) to use these new features with SqlClient.

Versions of SQL Server earlier than SQL Server 2008 only had two data types for working with date and time values: datetime and smalldatetime. Both of these data types contain both the date value and a time value, which makes it difficult to work with only date or only time values. Also, these data types only support dates that occur after the introduction of the Gregorian calendar in England in 1753. Another limitation is that these older data types are not time-zone aware, which makes it difficult to work with data that originates from multiple time zones.

For more information about date and time types in SQL Server, see Date and Time Data Types and Functions.

Date/Time Data Types Introduced in SQL Server 2008

The following table describes the new date and time data types.

Date Format and Date Order

How SQL Server parses date and time values depends not only on the type system version and server version, but also on the server's default language and format settings. A date string that works for the date formats of one language might be unrecognizable if the query is executed by a connection that uses a different language and date format setting.

The Transact-SQL SET LANGUAGE statement implicitly sets the DATEFORMAT that determines the order of the date parts. You can use the SET DATEFORMAT Transact-SQL statement on a connection to disambiguate date values by ordering the date parts in MDY, DMY, YMD, YDM, MYD, or DYM order.

If you do not specify any DATEFORMAT for the connection, SQL Server uses the default language associated with the connection. For example, a date string of '01/02/03' would be interpreted as MDY (January 2, 2003) on a server with a language setting of United States English, and as DMY (February 1, 2003) on a server with a language setting of British English. The year is determined by using SQL Server's cutoff year rule, which defines the cutoff date for assigning the century value. For more information, see two digit year cutoff Option.

For more information about how SQL Server interprets date and time data, see Using Date and Time Data.

Date/Time Data Types and Parameters

The following enumerations have been added to SqlDbType to support the new date and time data types.

• SqlDbType.Date

• SqlDbType.Time

• SqlDbType.DateTime2

• SqlDbType.DateTimeOffSet

You can specify the data type of a SqlParameter by using one of the preceding SqlDbType enumerations.

You can also specify the type of a SqlParameter generically by setting the DbType property of a SqlParameter object to a particular DbType enumeration value. The following enumeration values have been added to DbType to support the datetime2 and datetimeoffset data types:

• DbType.DateTime2

• DbType.DateTimeOffset

These new enumerations supplement the Date, Time, and DateTime enumerations, which existed in earlier versions of the .NET Framework.

The .NET Framework data provider type of a parameter object is inferred from the .NET Framework type of the value of the parameter object, or from the DbType of the parameter object. No new System.Data.SqlTypes data types have been introduced to support the new date and time data types. The following table describes the mappings between the SQL Server 2008 date and time data types and the CLR data types.

SqlParameter Properties

The following table describes SqlParameter properties that are relevant to date and time data types.

Creating Parameters

You can create a SqlParameter object by using its constructor, or by adding it to a SqlCommandParameters collection by calling the Add method of the SqlParameterCollection. The Add method will take as input either constructor arguments or an existing parameter object.

The next sections in this topic provide examples of how to specify date and time parameters. For additional examples of working with parameters, see Configuring Parameters and Parameter Data Types and DataAdapter Parameters.

Date Example

The following code fragment demonstrates how to specify a date parameter.

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@Date";
parameter.SqlDbType = SqlDbType.Date;
parameter.Value = "2007/12/1";

Dim parameter As New SqlParameter()
parameter.ParameterName = "@Date"
parameter.SqlDbType = SqlDbType.Date
parameter.Value = "2007/12/1"

Time Example

The following code fragment demonstrates how to specify a time parameter.

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@time";
parameter.SqlDbType = SqlDbType.Time;
parameter.Value = DateTime.Parse("23:59:59").TimeOfDay;

Dim parameter As New SqlParameter()
parameter.ParameterName = "@Time"
parameter.SqlDbType = SqlDbType.Time
parameter.Value = DateTime.Parse("23:59:59").TimeOfDay;

Datetime2 Example

The following code fragment demonstrates how to specify a datetime2 parameter with both the date and time parts.

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@Datetime2";
parameter.SqlDbType = SqlDbType.DateTime2;
parameter.Value = DateTime.Parse("1666-09-02 1:00:00");

Dim parameter As New SqlParameter()
parameter.ParameterName = "@Datetime2"
parameter.SqlDbType = SqlDbType.DateTime2
parameter.Value = DateTime.Parse("1666-09-02 1:00:00");

DateTimeOffSet Example

The following code fragment demonstrates how to specify a DateTimeOffSet parameter with a date, a time, and a time zone offset of 0.

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@DateTimeOffSet";
parameter.SqlDbType = SqlDbType.DateTimeOffSet;
parameter.Value = DateTimeOffset.Parse("1666-09-02 1:00:00+0");

Dim parameter As New SqlParameter()
parameter.ParameterName = "@DateTimeOffSet"
parameter.SqlDbType = SqlDbType.DateTimeOffSet
parameter.Value = DateTimeOffset.Parse("1666-09-02 1:00:00+0");

AddWithValue

You can also supply parameters by using the AddWithValue method of a SqlCommand, as shown in the following code fragment. However, the AddWithValue method does not allow you to specify the DbType or SqlDbType for the parameter.

command.Parameters.AddWithValue(
    "@date", DateTimeOffset.Parse("16660902"));

command.Parameters.AddWithValue( _
    "@date", DateTimeOffset.Parse("16660902"))

The @date parameter could map to a date, datetime, or datetime2 data type on the server. When working with the new datetime data types, you must explicitly set the parameter's SqlDbType property to the data type of the instance. Using Variant or implicitly supplying parameter values can cause problems with backward compatibility with the datetime and smalldatetime data types.

The following table shows which SqlDbTypes are inferred from which CLR types:

Retrieving Date and Time Data

The following table describes methods that are used to retrieve SQL Server 2008 date and time values.

Specifying Date and Time Values as Literals

You can specify date and time data types by using a variety of different literal string formats, which SQL Server then evaluates at run time, converting them to internal date/time structures. SQL Server recognizes date and time data that is enclosed in single quotation marks ('). The following examples demonstrate some formats:

• Alphabetic date formats, such as 'October 15, 2006'.

• Numeric date formats, such as '10/15/2006'.

• Unseparated string formats, such as '20061015', which would be interpreted as October 15, 2006 if you are using the ISO standard date format.

Time values that are less than zero or greater than or equal to 24 hours will throw an ArgumentException.

SQL Server docs resources

For more information about working with date and time values in SQL Server, see the following articles.

See also

• SQL Server Data Type Mappings
• Configuring Parameters and Parameter Data Types
• SQL Server Data Types and ADO.NET
• ADO.NET Overview