datetime2 (Transact-SQL)datetime2 (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

定義日期,並結合了 24 小時制的時間。Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 可視為現有 datetime 類型的延伸,因其具有較大的日期範圍、較大的預設小數有效位數和選擇性的使用者指定有效位數。datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

datetime2 描述datetime2 description

屬性Property Value
語法Syntax datetime2 [ (毫秒精確度) ]datetime2 [ (fractional seconds precision) ]
使用量Usage DECLARE @MyDatetime2 datetime2(7)DECLARE @MyDatetime2 datetime2(7)

CREATE TABLE Table1 ( Column1 datetime2(7) )CREATE TABLE Table1 ( Column1 datetime2(7) )
預設的字串常值格式Default string literal format

(用於下層用戶端)(used for down-level client)
YYYY-MM-DD hh:mm:ss[.小數秒數]YYYY-MM-DD hh:mm:ss[.fractional seconds]

如需詳細資訊,請參閱下列的<下層用戶端的回溯相容性>一節。For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
日期範圍Date range 0001-01-01 到 31.12.990001-01-01 through 9999-12-31

公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日January 1,1 CE through December 31, 9999 CE
時間範圍Time range 00:00:00 到 23:59:59.999999900:00:00 through 23:59:59.9999999
時區位移範圍Time zone offset range NoneNone
元素範圍Element ranges YYYY 是代表年份的四位數字,範圍介於 0001 至 9999 之間。YYYY is a four-digit number, ranging from 0001 through 9999, that represents a year.

MM 是代表指定年份中某個月份的兩位數字,範圍介於 01 至 12 之間。MM is a two-digit number, ranging from 01 to 12, that represents a month in the specified year.

DD 是代表指定月份中某個日期的兩位數字,範圍介於 01 至 31 之間 (視月份而定)。DD is a two-digit number, ranging from 01 to 31 depending on the month, that represents a day of the specified month.

hh 是代表小時的兩位數字,範圍介於 00 至 23 之間。hh is a two-digit number, ranging from 00 to 23, that represents the hour.

mm 是代表分鐘的兩位數字,範圍介於 00 至 59 之間。mm is a two-digit number, ranging from 00 to 59, that represents the minute.

ss 是代表秒鐘的兩位數字,範圍介於 00 至 59 之間。ss is a two-digit number, ranging from 00 to 59, that represents the second.

n* 是代表小數秒數的零至七位數字,範圍介於 0 至 9999999 之間。n* is a zero- to seven-digit number from 0 to 9999999 that represents the fractional seconds. 在 Informatica 中,毫秒會在 n > 3 時遭到截斷。In Informatica, the fractional seconds will be truncated when n > 3.
字元長度Character length 最小 19 個位置 (YYYY-MM-DD hh:mm:ss),最大 27 個位置 (YYYY-MM-DD hh:mm:ss.0000000)19 positions minimum (YYYY-MM-DD hh:mm:ss ) to 27 maximum (YYYY-MM-DD hh:mm:ss.0000000)
有效位數,小數位數Precision, scale 0 至 7 位數,精確度為 100ns。0 to 7 digits, with an accuracy of 100ns. 預設有效位數是 7 位數。The default precision is 7 digits.
儲存體大小Storage size 不到 3 個有效位數為 6 個位元組。6 bytes for precision less than 3.
3 或4 個有效位數為 7 個位元組。7 bytes for precision 3 or 4.
所有其他有效位數均需要 8 個位元組。1All other precision require 8 bytes.1
精確度Accuracy 100 奈秒100 nanoseconds
預設值Default value 1900-01-01 00:00:001900-01-01 00:00:00
CalendarCalendar 西曆Gregorian
使用者自訂的小數秒數有效位數User-defined fractional second precision Yes
時區位移感知和保留Time zone offset aware and preservation No
日光節約感知Daylight saving aware No

1datetime2 值的第一個位元組會儲存值的有效位數,這表示 datetime2 值所需的實際儲存空間是上表說明的儲存體大小另加 1 個位元組,以儲存有效位數。1 The first byte of a datetime2 value stores the precision of the value, which means the actual storage required for a datetime2 value is the storage size indicated in the table above plus 1 additional byte to store the precision. 這使得 datetime2 值的最大大小變成 9 個位元組:1 個位元組儲存有效位數,加上 8 個位元組用於最大有效位數的資料儲存。This makes the maximum size of a datetime2 value 9 bytes - 1 byte stores precision plus 8 bytes for data storage at maximum precision.

如需資料類型中繼資料,請參閱 sys.systypes (Transact-SQL)TYPEPROPERTY (Transact-SQL)For data type metadata, see sys.systypes (Transact-SQL) or TYPEPROPERTY (Transact-SQL). 有效位數和小數位數是某些日期和時間資料類型的變數。Precision and scale are variable for some date and time data types. 若要取得資料行的有效位數和小數位數,請參閱 COLUMNPROPERTY (Transact-SQL)COL_LENGTH (Transact-SQL),或 sys.columns (Transact-SQL)To obtain the precision and scale for a column, see COLUMNPROPERTY (Transact-SQL), COL_LENGTH (Transact-SQL), or sys.columns (Transact-SQL).

datetime2 支援的字串常值格式Supported string literal formats for datetime2

下表列出 datetime2 支援的 ISO 8601 及 ODBC 字串常值格式。The following tables list the supported ISO 8601 and ODBC string literal formats for datetime2. 如需 datetime2 日期和時間部分之字母、數字、未分隔及時間格式的資訊,請參閱 date (Transact-SQL)time (Transact-SQL)For information about alphabetical, numeric, unseparated, and time formats for the date and time parts of datetime2, see date (Transact-SQL) and time (Transact-SQL).

ISO 8601ISO 8601 說明Descriptions
YYYY-MM-DDThh:mm:ss[.nnnnnnn]YYYY-MM-DDThh:mm:ss[.nnnnnnn]

YYYY-MM-DDThh:mm:ss[.nnnnnnn]YYYY-MM-DDThh:mm:ss[.nnnnnnn]
此格式不受 SET LANGUAGE 和 SET DATEFORMAT 工作階段地區設定的影響。This format is not affected by the SET LANGUAGE and SET DATEFORMAT session locale settings. T、冒號 (:) 和句號 (.) 會包含在字串常值中,例如 '2007-05-02T19:58:47.1234567'。The T, the colons (:) and the period (.) are included in the string literal, for example '2007-05-02T19:58:47.1234567'.
ODBCODBC 描述Description
{ ts 'yyyy-mm-dd hh:mm:ss[.小數秒數]' }{ ts 'yyyy-mm-dd hh:mm:ss[.fractional seconds]' } ODBC API 專用:ODBC API specific:

小數點右邊的位數 (代表小數秒數) 可指定為 0 至 7 (100 奈秒)。The number of digits to the right of the decimal point, which represents the fractional seconds, can be specified from 0 up to 7 (100 nanoseconds).

ANSI 和 ISO 8601 合規性ANSI and ISO 8601 compliance

datetime 的 ANSI 與 ISO 8601 合規性適用於 datetime2The ANSI and ISO 8601 compliance of date and time apply to datetime2.

下層用戶端的回溯相容性Backward Compatibility for Down-level Clients

有些下層用戶端不支援 timedatedatetime2datetimeoffset 資料類型。Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. 下表顯示在 SQL ServerSQL Server 的上層執行個體與下層用戶端之間的類型對應。The following table shows the type mapping between an up-level instance of SQL ServerSQL Server and down-level clients.

SQL ServerSQL Server 資料類型data type 傳遞至下層用戶端的預設字串常值格式Default string literal format passed to down-level client 下層 ODBCDown-level ODBC 下層 OLEDBDown-level OLEDB 下層 JDBCDown-level JDBC 下層 SQLCLIENTDown-level SQLCLIENT
timetime hh:mm:ss[.nnnnnnn]hh:mm:ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHARSQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STRDBTYPE_WSTRor DBTYPE_STR Java.sql.StringJava.sql.String 字串或 SqStringString or SqString
datedate YYYY-MM-DDYYYY-MM-DD SQL_WVARCHAR 或 SQL_VARCHARSQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STRDBTYPE_WSTRor DBTYPE_STR Java.sql.StringJava.sql.String 字串或 SqStringString or SqString
datetime2datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn]YYYY-MM-DD hh:mm:ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHARSQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STRDBTYPE_WSTRor DBTYPE_STR Java.sql.StringJava.sql.String 字串或 SqStringString or SqString
datetimeoffsetdatetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mmYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm SQL_WVARCHAR 或 SQL_VARCHARSQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STRDBTYPE_WSTRor DBTYPE_STR Java.sql.StringJava.sql.String 字串或 SqStringString or SqString

轉換日期和時間資料Converting date and time data

當您轉換成日期與時間資料類型時,SQL ServerSQL Server 會拒絕所有無法辨識為日期或時間的值。When you convert to date and time data types, SQL ServerSQL Server rejects all values it cannot recognize as dates or times. 如需 CAST 及 CONVERT 函式與日期和時間資料搭配使用的資訊,請參閱 CAST 和 CONVERT (Transact-SQL)For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL)

將其他日期與時間類型轉換成 datetime2 資料類型Converting other date and time types to the datetime2 data type

本節描述當其他日期與時間資料類型轉換成 datetime2 資料類型時,可能發生的狀況。This section describes what occurs when other date and time data types are converted to the datetime2 data type.

date 轉換時,年、月和日都會複製。When the conversion is from date, the year, month and day are copied. 時間元件會設定為 00:00:00.0000000。The time component is set to 00:00:00.0000000. 下列程式碼顯示將 date 值轉換成 datetime2 值的結果。The following code shows the results of converting a date value to a datetime2 value.

DECLARE @date date = '12-21-16';
DECLARE @datetime2 datetime2 = @date;

SELECT @datetime2 AS '@datetime2', @date AS '@date';
  
--Result  
--@datetime2                  @date
----------------------------- ----------
--2016-12-21 00:00:00.0000000 2016-12-21

time(n) 轉換時,會複製時間元件,而日期元件會設定為 '1900-01-01'。When the conversion is from time(n), the time component is copied, and the date component is set to '1900-01-01'. 下列範例顯示將 time(7) 值轉換成 datetime2 值的結果。The following example shows the results of converting a time(7) value to a datetime2 value.

DECLARE @time time(7) = '12:10:16.1234567';
DECLARE @datetime2 datetime2 = @time;

SELECT @datetime2 AS '@datetime2', @time AS '@time';
  
--Result  
--@datetime2                  @time
----------------------------- ----------------
--1900-01-01 12:10:16.1234567 12:10:16.1234567

smalldatetime 轉換時,會複製小時和分鐘。When the conversion is from smalldatetime, the hours and minutes are copied. 秒和小數秒數會設定為 0。The seconds and fractional seconds are set to 0. 下列程式碼顯示將 smalldatetime 值轉換成 datetime2 值的結果。The following code shows the results of converting a smalldatetime value to a datetime2 value.

DECLARE @smalldatetime smalldatetime = '12-01-16 12:32';
DECLARE @datetime2 datetime2 = @smalldatetime;

SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime'; 
  
--Result  
--@datetime2                  @smalldatetime
----------------------------- -----------------------
--2016-12-01 12:32:00.0000000 2016-12-01 12:32:00 

datetimeoffset(n) 轉換時,會複製日期和時間元件。When the conversion is from datetimeoffset(n), the date and time components are copied. 時區則會被截斷。The time zone is truncated. 下列範例顯示將 datetimeoffset(7) 值轉換成 datetime2 值的結果。The following example shows the results of converting a datetimeoffset(7) value to a datetime2 value.

DECLARE @datetimeoffset datetimeoffset(7) = '2016-10-23 12:45:37.1234567 +10:0';
DECLARE @datetime2 datetime2 = @datetimeoffset;

SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset'; 
  
--Result  
--@datetime2                  @datetimeoffset
----------------------------- ----------------------------------
--2016-10-23 12:45:37.1234567 2016-10-23 12:45:37.1234567 +10:00

當轉換來自 datetime,會複製日期和時間。When the conversion is from datetime, the date and time are copied. 小數有效位數會延伸至 7 位數。The fractional precision is extended to 7 digits. 下列範例顯示將 datetime 值轉換成 datetime2 值的結果。The following example shows the results of converting a datetime value to a datetime2 value.

DECLARE @datetime datetime = '2016-10-23 12:45:37.333';
DECLARE @datetime2 datetime2 = @datetime;

SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime';
   
--Result  
--@datetime2                  @datetime
------------------------- ---------------------------
--2016-10-23 12:45:37.3333333 2016-10-23 12:45:37.333

注意

在資料庫相容性層級 130 下,藉由考量小數部分的毫秒從 datetime 隱含轉換至 datetime2 資料類型顯示改善的精確度,會導致不同的轉換值,如上面範例所示。Under database compatibility level 130, implicit conversions from datetime to datetime2 data types show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values, as seen in the example above. 只要 datetime 和 datetime2 資料類型之間存在混合的比較案例,就明確轉換為 datetime2 資料類型。Use explicit casting to datetime2 datatype whenever a mixed comparison scenario between datetime and datetime2 datatypes exists. 如需詳細資訊,請參閱此篇 Microsoft 支援服務文章For more information, refer to this Microsoft Support Article.

將字串常值轉換為 datetime2Converting String Literals to datetime2

如果整個字串皆是有效的格式,即可從字串常值轉換為日期與時間類型。Conversions from string literals to date and time types are permitted if all parts of the strings are in valid formats. 否則,就會引發執行階段錯誤。Otherwise, a runtime error is raised. 從日期與時間類型轉換為字串常值的明確轉換不會指定樣式的隱含轉換,一律會採用目前工作階段的預設格式。Implicit conversions or explicit conversions that do not specify a style, from date and time types to string literals will be in the default format of the current session. 下表是字串常值轉換為 datetime2 資料類型的規則。The following table shows the rules for converting a string literal to the datetime2 data type.

輸入字串常值Input string literal datetime2(n)datetime2(n)
ODBC DATEODBC DATE ODBC 字串常值會對應到 datetime 資料類型。ODBC string literals are mapped to the datetime data type. 任何將 ODBC DATETIME 常值指派成 datetime2 類型的作業,皆會根據轉換規則,執行 datetime 與此類型之間的隱含轉換。Any assignment operation from ODBC DATETIME literals into datetime2 types will cause an implicit conversion between datetime and this type as defined by the conversion rules.
ODBC TIMEODBC TIME 請參閱先前的 ODBC DATE 規則。See previous ODBC DATE rule.
ODBC DATETIMEODBC DATETIME 請參閱先前的 ODBC DATE 規則。See previous ODBC DATE rule.
僅限 DATEDATE only TIME 部分預設為 00:00:00。The TIME part defaults to 00:00:00.
僅限 TIMETIME only DATE 部分預設為 1900-1-1。The DATE part defaults to 1900-1-1.
僅限 TIMEZONETIMEZONE only 提供預設值。Default values are supplied.
DATE + TIMEDATE + TIME 一般Trivial
DATE + TIMEZONEDATE + TIMEZONE 不允許。Not allowed.
TIME + TIMEZONETIME + TIMEZONE DATE 部分預設為 1900-1-1。The DATE part defaults to 1900-1-1. 忽略 TIMEZONE 輸入。TIMEZONE input is ignored.
DATE + TIME + TIMEZONEDATE + TIME + TIMEZONE 將使用本機 DATETIME。The local DATETIME will be used.

範例Examples

下列範例會比較將字串轉換成各種 datetime 資料類型的結果。The following example compares the results of casting a string to each date and time data type.

SELECT   
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'   
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'   
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS   
        'smalldatetime'   
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'   
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS   
        'datetime2'  
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS   
        'datetimeoffset';  

以下為結果集。Here is the result set.

資料類型Data type 輸出Output
timetime 12:35:29.12:35:29. 12345671234567
datedate 2007-05-082007-05-08
smalldatetimesmalldatetime 2007-05-08 12:35:002007-05-08 12:35:00
datetimedatetime 2007-05-08 12:35:29.1232007-05-08 12:35:29.123
datetime2datetime2 2007-05-08 12:35:29.2007-05-08 12:35:29. 12345671234567
datetimeoffsetdatetimeoffset 2007-05-08 12:35:29.1234567 +12:152007-05-08 12:35:29.1234567 +12:15

另請參閱See also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)