日期和时间数据Date and Time Data

SQL Server 2008 引入了用于处理日期和时间信息的新数据类型。SQL Server 2008 introduces new data types for handling date and time information. 新的数据类型包括单独的日期和时间类型以及具有更大范围、更高精度和更强时区感知能力的扩展数据类型。The new data types include separate types for date and time, and expanded data types with greater range, precision, and time-zone awareness. 从 .NET Framework 3.5 Service Pack (SP) 1 开始,适用于 SQL Server 的 .NET Framework 数据提供程序 (System.Data.SqlClient) 完全支持 SQL Server 2008 数据库引擎的所有新功能。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. 您必须安装 .NET Framework 3.5 SP1(或更高版本)才能将这些新功能与 SqlClient 一起使用。You must install the .NET Framework 3.5 SP1 (or later) to use these new features with SqlClient.

SQL Server 2008 之前的 SQL Server 版本仅具有两种用于处理日期和时间值的数据类型:datetimesmalldatetimeVersions 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. 此外,这些数据类型仅支持自 1753 年英国引入公历日历以来的日期。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.

有关 SQL Server 数据类型的完整文档可从 SQL Server 联机丛书中获得。Complete documentation for SQL Server data types is available in SQL Server Books Online. 下表列出了有关日期和时间数据的因版本而异的入门级主题。The following table lists the version-specific entry-level topics for date and time data.

SQL Server 联机丛书SQL Server Books Online

  1. 使用日期和时间数据Using Date and Time Data

SQL Server 2008 中引入的日期/时间数据类型Date/Time Data Types Introduced in SQL Server 2008

下表描述了新的日期和时间数据类型。The following table describes the new date and time data types.

SQL Server 数据类型SQL Server data type 描述Description
date date 数据类型的范围从 01 年 1 月 1 日到 9999 年 12 月 31 日,精度为 1 天。The date data type has a range of January 1, 01 through December 31, 9999 with an accuracy of 1 day. 默认值为 1900 年 1月 1日。The default value is January 1, 1900. 存储大小为 3 字节。The storage size is 3 bytes.
time time 数据类型仅存储时间值,并且采用 24 小时制。The time data type stores time values only, based on a 24-hour clock. time 数据类型的范围从 00:00:00.0000000 到 23:59:59.9999999,精确到 100 毫微秒。The time data type has a range of 00:00:00.0000000 through 23:59:59.9999999 with an accuracy of 100 nanoseconds. 默认值为 00:00:00.0000000(午夜)。The default value is 00:00:00.0000000 (midnight). time 数据类型支持用户定义的小数秒精度,存储大小根据指定的精度在 3 字节到 6 字节之间变化。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 datetime2 数据类型将 datetime 数据类型的范围和精度组合成单个数据类型。The datetime2 data type combines the range and precision of the date and time data types into a single data type.

默认值和字符串格式与 datetime 数据类型中定义的相同。The default values and string literal formats are the same as those defined in the date and time data types.
datetimeoffset datetimeoffset 数据类型具有 datetime2 的所有功能,并附加了时区偏移量。The datetimeoffset data type has all the features of datetime2 with an additional time zone offset. 时区偏移量表示为 [+|-] HH: MM。The time zone offset is represented as [+|-] HH:MM. HH 是范围从 00 到 14 的 2 位数,表示时区偏移量的小时数。HH is 2 digits ranging from 00 to 14 that represent the number of hours in the time zone offset. MM 是范围从 00 到 59 的 2 位数,表示时区偏移量的附加分钟数。MM is 2 digits ranging from 00 to 59 that represent the number of additional minutes in the time zone offset. 时间格式支持的精度为 100 毫微秒。Time formats are supported to 100 nanoseconds. 必需的 + 或 - 符号指示在 UTC(通用协调时间或格林尼治标准时间)中是加上还是减去时区偏移量以获取本地时间。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.

备注

有关使用 Type System Version 关键字的更多信息,请参见 ConnectionStringFor more information about using the Type System Version keyword, see ConnectionString.

日期格式和日期顺序Date Format and Date Order

SQL Server 分析日期和时间值的方式不仅依赖于类型系统版本和服务器版本,还依赖于服务器的默认语言和格式设置。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.

Transact-SQL SET LANGUAGE 语句隐式设置可确定日期各个部分的顺序的 DATEFORMAT。The Transact-SQL SET LANGUAGE statement implicitly sets the DATEFORMAT that determines the order of the date parts. 可以对连接使用 SET DATEFORMAT Transact-SQL 语句,通过按 MDY、DMY、YMD、YDM、MYD 或 DYM 顺序对日期的各个部分进行排序来消除日期值的歧义。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.

如果没有为连接指定任何 DATEFORMAT,则 SQL Server 将使用与连接关联的默认语言。If you do not specify any DATEFORMAT for the connection, SQL Server uses the default language associated with the connection. 例如,在使用美国英语语言设置的服务器上,日期字符串“01/02/03”将解释为 MDY(2003 年 1 月 2 日),而在使用英国英语语言设置的服务器上,将解释为 DMY(2003 年 2 月 1 日)。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. 年份是通过使用 SQL Server 的截止年份规则确定的,该规则定义用于分配世纪值的截止日期。The year is determined by using SQL Server's cutoff year rule, which defines the cutoff date for assigning the century value. 有关详细信息,请参阅 SQL Server 联机丛书中的两位数年份截止选项For more information, see two digit year cutoff Option in SQL Server Books Online.

备注

从字符串格式转换为 datetimedatetime2datetimeoffset 时不支持 YDM 日期格式。The YDM date format is not supported when converting from a string format to date, time, datetime2, or datetimeoffset.

有关 SQL Server 如何解释日期和时间数据的详细信息,请参阅 SQL Server 2008 联机丛书中的使用日期和时间数据For more information about how SQL Server interprets date and time data, see Using Date and Time Data in SQL Server 2008 Books Online.

日期/时间数据类型和参数Date/Time Data Types and Parameters

SqlDbType 中已添加了下面的枚举,以支持新的日期和时间数据类型。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

您可以使用前面SqlParameter SqlDbType的枚举之一来指定的数据类型。You can specify the data type of a SqlParameter by using one of the preceding SqlDbType enumerations.

备注

不能将的DbType属性SqlParameter设置为SqlDbType.DateYou cannot set the DbType property of a SqlParameter to SqlDbType.Date.

也可以通过将 SqlParameter 对象的 DbType 属性设置为特定的 SqlParameter 枚举值,按照通常的方式来指定 DbType 的类型。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. DbType 中已添加了下面的枚举值,以支持 datetime2datetimeoffset 数据类型:The following enumeration values have been added to DbType to support the datetime2 and datetimeoffset data types:

  • DbType.DateTime2DbType.DateTime2

  • DbType.DateTimeOffsetDbType.DateTimeOffset

这些新枚举补充了早期版本的 .NET Framework 中存在的 DateTimeDateTime 枚举。These new enumerations supplement the Date, Time, and DateTime enumerations, which existed in earlier versions of the .NET Framework.

某一参数对象的 .NET Framework 数据提供程序类型是从该参数对象的 .NET Framework 类型或该参数对象的 DbType 推断而来的。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. 没有引入新的 System.Data.SqlTypes 数据类型来支持新的日期和时间数据类型。No new System.Data.SqlTypes data types have been introduced to support the new date and time data types. 下表说明 SQL Server 2008 日期和时间数据类型和 CLR 数据类型之间的映射。The following table describes the mappings between the SQL Server 2008 date and time data types and the CLR data types.

SQL Server 数据类型SQL Server data type .NET Framework 类型.NET Framework type System.Data.SqlDbTypeSystem.Data.SqlDbType System.Data.DbTypeSystem.Data.DbType
datedate System.DateTimeSystem.DateTime 日期Date 日期Date
timetime System.TimeSpanSystem.TimeSpan 时间Time 时间Time
datetime2datetime2 System.DateTimeSystem.DateTime DateTime2DateTime2 DateTime2DateTime2
datetimeoffsetdatetimeoffset System.DateTimeOffsetSystem.DateTimeOffset DateTimeOffsetDateTimeOffset DateTimeOffsetDateTimeOffset
DATETIMEdatetime System.DateTimeSystem.DateTime DateTimeDateTime DateTimeDateTime
smalldatetimesmalldatetime System.DateTimeSystem.DateTime DateTimeDateTime DateTimeDateTime

SqlParameter 属性SqlParameter Properties

下表描述了与日期和时间数据类型相关的 SqlParameter 属性。The following table describes SqlParameter properties that are relevant to date and time data types.

属性Property 描述Description
IsNullable 获取或设置值是否可以为 null。Gets or sets whether a value is nullable. 在向服务器发送 null 参数值时,必须指定 DBNull 而非 null(在 Visual Basic 中为 Nothing)。When you send a null parameter value to the server, you must specify DBNull, rather than null (Nothing in Visual Basic). 有关数据库 null 值的详细信息,请参阅 Handling Null ValuesFor 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 获取或设置要为TimeDateTime2DateTimeOffset解析的值的时间部分的小数位数。Gets or sets the number of decimal places to which the time portion of the value is resolved for Time, DateTime2,and DateTimeOffset. 默认值为 0。这意味着实际小数位数从值推断出来并发送给服务器。The default value is 0, which means that the actual scale is inferred from the value and sent to the server.
Size 对于日期和时间数据类型,忽略此设置。Ignored for date and time data types.
Value 获取或设置参数值。Gets or sets the parameter value.
SqlValue 获取或设置参数值。Gets or sets the parameter value.

备注

小于 0 或大于等于 24 小时的时间值将引发 ArgumentExceptionTime values that are less than zero or greater than or equal to 24 hours will throw an ArgumentException.

创建参数Creating Parameters

可以通过以下方法来创建 SqlParameter 对象:使用相应的构造函数;或者通过调用 SqlCommandParameters 方法将其添加到 AddSqlParameterCollection 集合。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. Add 方法将采用构造函数自变量或现有的参数对象用作输入。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. 有关使用参数的其他示例,请参阅配置参数和参数数据类型DataAdapter 参数For additional examples of working with parameters, see Configuring Parameters and Parameter Data Types and DataAdapter Parameters.

date 示例Date Example

下面的代码段演示如何指定 date 参数。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 示例Time Example

下面的代码段演示如何指定 time 参数。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 示例Datetime2 Example

下面的代码段演示如何指定同时具有日期和时间部分的 datetime2 参数。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 示例DateTimeOffSet Example

下面的代码段演示如何指定具有日期、时间和偏移量为 0 的时区的 DateTimeOffSet 参数。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");  

AddWithValueAddWithValue

也可以通过使用 AddWithValueSqlCommand 方法来提供参数,如下面的代码段所示。You can also supply parameters by using the AddWithValue method of a SqlCommand, as shown in the following code fragment. 不过,不能使用 AddWithValue 方法为参数指定 DbTypeSqlDbTypeHowever, 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"))  

参数可以映射到服务器上datedatetime、或datetime2数据类型。 @dateThe @date parameter could map to a date, datetime, or datetime2 data type on the server. 使用新的 datetime 数据类型时,必须将参数的 SqlDbType 属性显式设置为实例的数据类型。When working with the new datetime data types, you must explicitly set the parameter's SqlDbType property to the data type of the instance. 如果使用 Variant 或隐式提供参数值,则会导致与 datetimesmalldatetime 数据类型的向后兼容问题。Using Variant or implicitly supplying parameter values can cause problems with backward compatibility with the datetime and smalldatetime data types.

下表显示可从哪些 CLR 类型中推断出哪些 SqlDbTypesThe following table shows which SqlDbTypes are inferred from which CLR types:

CLR 类型CLR type 推断出的 SqlDbTypeInferred SqlDbType
DateTimeDateTime SqlDbType.DateTimeSqlDbType.DateTime
TimeSpanTimeSpan SqlDbType.TimeSqlDbType.Time
DateTimeOffsetDateTimeOffset SqlDbType.DateTimeOffsetSqlDbType.DateTimeOffset

检索日期和时间数据Retrieving Date and Time Data

下表说明可用于检索 SQL Server 2008 日期和时间值的方法。The following table describes methods that are used to retrieve SQL Server 2008 date and time values.

SqlClient 方法SqlClient method 描述Description
GetDateTime DateTime 结构形式检索指定的列值。Retrieves the specified column value as a DateTime structure.
GetDateTimeOffset DateTimeOffset 结构形式检索指定的列值。Retrieves the specified column value as a DateTimeOffset structure.
GetProviderSpecificFieldType 返回作为该字段基础提供程序特定类型的类型。Returns the type that is the underlying provider-specific type for the field. 返回新日期和时间类型的与 GetFieldType 相同的类型。Returns the same types as GetFieldType for new date and time types.
GetProviderSpecificValue 检索指定列的值。Retrieves the value of the specified column. 返回新日期和时间类型的与 GetValue 相同的类型。Returns the same types as GetValue for the new date and time types.
GetProviderSpecificValues 检索指定数组中的值。Retrieves the values in the specified array.
GetSqlString SqlString 形式检索列值。Retrieves the column value as a SqlString. 如果数据无法表示为 InvalidCastException,则发生 SqlStringAn InvalidCastException occurs if the data cannot be expressed as a SqlString.
GetSqlValue 检索列数据作为其默认 SqlDbTypeRetrieves column data as its default SqlDbType. 返回新日期和时间类型的与 GetValue 相同的类型。Returns the same types as GetValue for the new date and time types.
GetSqlValues 检索指定数组中的值。Retrieves the values in the specified array.
GetString 如果 Type System Version 设置为 SQL Server 2005,则以字符串形式检索列值。Retrieves the column value as a string if the Type System Version is set to SQL Server 2005. 如果数据无法表示为字符串,则发生 InvalidCastExceptionAn InvalidCastException occurs if the data cannot be expressed as a string.
GetTimeSpan TimeSpan 结构形式检索指定的列值。Retrieves the specified column value as a TimeSpan structure.
GetValue 检索指定的列值作为其基础 CLR 类型。Retrieves the specified column value as its underlying CLR type.
GetValues 检索数组中的列值。Retrieves column values in an array.
GetSchemaTable 返回一个 DataTable,说明结果集的元数据。Returns a DataTable that describes the metadata of the result set.

备注

对于 SQL Server 中在进程内执行的代码,不支持新的日期和时间 SqlDbTypesThe 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

可以通过使用各种不同的文字字符串格式指定日期和时间数据类型,然后 SQL Server 在运行时执行相关计算以将这些文字字符串转换为内部的日期/时间结构。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 可识别用单引号 (') 括起来的日期和时间数据。SQL Server recognizes date and time data that is enclosed in single quotation marks ('). 下面的示例演示了一些格式:The following examples demonstrate some formats:

  • 字母日期格式,例如 'October 15, 2006'Alphabetic date formats, such as 'October 15, 2006'.

  • 数值日期格式,例如 '10/15/2006'Numeric date formats, such as '10/15/2006'.

  • 未分隔的字符串格式,例如 '20061015'。如果您使用的是 ISO 标准日期格式,则该字符串将解释为 2006 年 10 月 15 日。Unseparated string formats, such as '20061015', which would be interpreted as October 15, 2006 if you are using the ISO standard date format.

备注

在 SQL Server 联机丛书中,您可以找到有关日期和时间数据类型的所有文字字符串格式以及其他功能的完整文档。You can find complete documentation for all of the literal string formats and other features of the date and time data types in SQL Server Books Online.

小于 0 或大于等于 24 小时的时间值将引发 ArgumentExceptionTime values that are less than zero or greater than or equal to 24 hours will throw an ArgumentException.

SQL Server 2008 联机丛书中的资源Resources in SQL Server 2008 Books Online

有关如何在 SQL Server 2008 中使用日期和时间值的更多信息,请参见 SQL Server 2008 联机丛书中的以下资源。For more information about working with date and time values in SQL Server 2008, see the following resources in SQL Server 2008 Books Online.

主题Topic 描述Description
日期和时间数据类型及函数(Transact-sql)Date and Time Data Types and Functions (Transact-SQL) 概述所有 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.
数据类型(Transact-sql)Data Types (Transact-SQL) 介绍 SQL Server 2008 中的系统数据类型。Describes system data types in SQL Server 2008.

请参阅See also