日期和時間資料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 開始,.NET Framework Data Provider for SQL Server (System.Data.SqlClient) 就會針對 SQL Server 2008 Database Engine 的所有新功能提供完整支援。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 documentation

  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 表示時區位移中的 2 位數時數,範圍介於 00 至 14 之間。HH is 2 digits ranging from 00 to 14 that represent the number of hours in the time zone offset. MM 是代表時區位移中額外分鐘數的 2 位數,範圍介於 00 至 59 之間。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. 您可以利用 MDY、DMY、YMD、YDM、MYD 或 DYM 順序來排序日期部分,以在連線上使用 SET DATEFORMAT Transact-SQL 陳述式來釐清日期值。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. 如需詳細資訊,請參閱 兩位數年份截止選項For more information, see two digit year cutoff Option.

注意

從字串格式轉換為 datetimedatetime2datetimeoffset 時,不支援 YDM 日期格式。The YDM date format is not supported when converting from a string format to date, time, datetime2, or datetimeoffset.

如需 SQL Server 如何解讀日期和時間資料的詳細資訊,請參閱 使用日期和時間資料For more information about how SQL Server interprets date and time data, see Using Date and Time Data.

日期/時間資料型別和參數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

您可以使用上述其中一個 SqlDbType 列舉來指定 SqlParameter 的資料類型。You can specify the data type of a SqlParameter by using one of the preceding SqlDbType enumerations.

注意

您無法將 SqlParameterDbType 屬性設定為 SqlDbType.DateYou cannot set the DbType property of a SqlParameter to SqlDbType.Date.

您也可以藉由將 SqlParameter 物件的 DbType 屬性設定為特定的 DbType 列舉值,以一般的方法指定 SqlParameter 的類型。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 型別,或從參數物件的 DbType 推斷出參數物件的 .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. 尚未引進任何新的 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 TimeTime TimeTime
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 的詳細資訊,請參閱 處理 Null 值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 取得或設定針對 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.

注意

小於零或者大於或等於 24 小時的時間值將會擲回 ArgumentExceptionTime values that are less than zero or greater than or equal to 24 hours will throw an ArgumentException.

建立參數Creating Parameters

您可以使用 SqlParameter 物件的建構函式 (Constructor),或將它加入至 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

您也可以使用 SqlCommandAddWithValue 方法來提供參數,如下列程式碼片段所示。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"))  

@date 參數可對應至伺服器上的 datedatetimedatetime2 資料類型。The @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. 如果無法將資料表示為 SqlString,就會發生 InvalidCastExceptionAn 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.

小於零或者大於或等於 24 小時的時間值將會擲回 ArgumentExceptionTime values that are less than zero or greater than or equal to 24 hours will throw an ArgumentException.

《SQL Server 線上叢書》中的資源Resources in SQL Server Books Online

如需在 SQL Server 中使用日期和時間值的詳細資訊,請參閱 SQL Server 線上叢書中的下列資源。For more information about working with date and time values in SQL Server, see the following resources in SQL Server 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 中的系統資料類型。Describes system data types in SQL Server.

另請參閱See also