Language
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
SQL Server 2008 introduced separate data types for date and time, and expanded data types with greater range, precision, and time zone awareness. Use the Microsoft.Data.SqlClient NuGet package to access these data types from client applications. For the examples in this article, import Microsoft.Data.SqlClient and System.Data in C# or Visual Basic. The common SQL value types remain in System.Data.SqlTypes.
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.
| SQL Server data type | Description |
|---|---|
date |
The date data type has a range of January 1, 01 through December 31, 9999 with an accuracy of 1 day. The default value is January 1, 1900. The storage size is 3 bytes. |
time |
The time data type stores time values only, based on a 24-hour clock. The time data type has a range of 00:00:00.0000000 through 23:59:59.9999999 with an accuracy of 100 nanoseconds. The default value is 00:00:00.0000000 (midnight). The time data type supports user-defined fractional second precision, and the storage size varies from 3 to 6 bytes, based on the precision specified. |
datetime2 |
The datetime2 data type combines the range and precision of the date and time data types into a single data type.The default values and string literal formats are the same as those defined in the date and time data types. |
datetimeoffset |
The datetimeoffset data type has all the features of datetime2 with an additional time zone offset. The time zone offset is represented as [+|-] HH:MM. HH is 2 digits ranging from 00 to 14 that represent the number of hours in the time zone offset. MM is 2 digits ranging from 00 to 59 that represent the number of additional minutes in the time zone offset. Time formats are supported to 100 nanoseconds. The mandatory + or - sign indicates whether the time zone offset is added or subtracted from UTC (Universal Time Coordinate or Greenwich Mean Time) to obtain the local time. |
Note
For more information about using the Type System Version keyword, see ConnectionString.
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.
Note
The YDM date format is not supported when converting from a string format to date, time, datetime2, or datetimeoffset.
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.DateSqlDbType.TimeSqlDbType.DateTime2SqlDbType.DateTimeOffset
To specify the data type for the SqlParameter object, use one of the preceding SqlDbType enumeration values.
Note
You cannot set the DbType property of a SqlParameter to SqlDbType.Date.
To specify the parameter type generically, set the DbType property to the DbType enumeration value. The following DbType values support the datetime2 and datetimeoffset data types:
DbType.DateTime2DbType.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.
| SQL Server data type | .NET Framework type | System.Data.SqlDbType |
System.Data.DbType |
|---|---|---|---|
date |
System.DateTime |
Date |
Date |
time |
System.TimeSpan |
Time |
Time |
datetime2 |
System.DateTime |
DateTime2 |
DateTime2 |
datetimeoffset |
System.DateTimeOffset |
DateTimeOffset |
DateTimeOffset |
datetime |
System.DateTime |
DateTime |
DateTime |
smalldatetime |
System.DateTime |
DateTime |
DateTime |
SqlParameter Properties
The following table describes SqlParameter properties that are relevant to date and time data types.
| Property | Description |
|---|---|
| IsNullable | Gets or sets whether a value is nullable. When you send a null parameter value to the server, you must specify DBNull, rather than null (Nothing in Visual Basic). For more information about database nulls, see Handling Null Values. |
| Precision | Gets or sets the maximum number of digits used to represent the value. This setting is ignored for date and time data types. |
| Scale | Gets or sets fractional-second precision for Time, DateTime2, and DateTimeOffset. If you don't explicitly set the scale, the driver sends scale 7. Explicitly setting the scale to 0 requests zero fractional-second digits. |
| Size | Ignored for date and time data types. |
| Value | Gets or sets the parameter value. |
| SqlValue | Gets or sets the parameter value. |
Note
Time values that are less than zero or greater than or equal to 24 hours will throw an ArgumentException.
Creating Parameters
Create SqlParameter objects with the constructor, or call the Add method on the command's Parameters collection. The Add method takes 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-02T01:00:00+00:00");
Dim parameter As New SqlParameter()
parameter.ParameterName = "@DateTimeOffSet"
parameter.SqlDbType = SqlDbType.DateTimeOffset
parameter.Value = DateTimeOffset.Parse("1666-09-02T01:00:00+00:00")
AddWithValue
To supply parameters, you can also call AddWithValue on the command's Parameters collection, as shown in the following code fragment. However, AddWithValue doesn't let you specify the DbType or SqlDbType for the parameter.
command.Parameters.AddWithValue(
"@date", DateTimeOffset.Parse("1666-09-02T01:00:00+00:00"));
command.Parameters.AddWithValue( _
"@date", DateTimeOffset.Parse("1666-09-02T01:00:00+00:00"))
For this DateTimeOffset value, AddWithValue infers SqlDbType.DateTimeOffset. To target a different SQL Server date or time type, set the parameter's SqlDbType property explicitly.
The following table shows which SqlDbTypes are inferred from which CLR types:
| CLR type | Inferred SqlDbType |
|---|---|
DateTime |
SqlDbType.DateTime |
TimeSpan |
SqlDbType.Time |
DateTimeOffset |
SqlDbType.DateTimeOffset |
Retrieving Date and Time Data
The following table describes methods that are used to retrieve SQL Server 2008 date and time values.
| SqlClient method | Description |
|---|---|
| GetDateTime | Retrieves the specified column value as DateTime. |
| GetDateTimeOffset | Retrieves the specified column value as DateTimeOffset. |
| GetProviderSpecificFieldType | Returns the type that is the underlying provider-specific type for the field. Returns the same types as GetFieldType for new date and time types. |
| GetProviderSpecificValue | Retrieves the value of the specified column. Returns the same types as GetValue for the new date and time types. |
| GetProviderSpecificValues | Retrieves the values in the specified array. |
| GetSqlString | Retrieves the column value as SqlString. The method throws InvalidCastException if the data cannot be expressed as SqlString. |
| GetSqlValue | Retrieves column data as its default SqlDbType. Returns the same types as GetValue for the new date and time types. |
| GetSqlValues | Retrieves the values in the specified array. |
| GetString | Retrieves the column value as a string if the Type System Version is set to SQL Server 2005. The method throws InvalidCastException if the data cannot be expressed as a string. |
| GetTimeSpan | Retrieves the specified column value as TimeSpan. |
| GetValue | Retrieves the specified column value as its underlying CLR type. |
| GetValues | Retrieves column values in an array. |
| GetSchemaTable | Returns the result set's metadata in the DataTable object. |
Note
The new date and time SqlDbTypes are not supported for code that is executing in-process in SQL Server. An exception will be raised if one of these types is passed to the server.
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 runtime, 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.
| Article | Description |
|---|---|
| Date and Time Data Types and Functions (Transact-SQL) | Provides an overview of all Transact-SQL date and time data types and functions. |
| Using Date and Time Data | Provides information about the date and time data types and functions, and examples of using them. |
| Data Types (Transact-SQL) | Describes system data types in SQL Server. |