datetimeoffset (Transact-SQL)datetimeoffset (Transact-SQL)

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

定義日期,並結合了具有時區感知並以 24 小時制為基礎的當日時間。Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

datetimeoffset 描述datetimeoffset description

屬性Property ReplTest1Value
語法Syntax datetimeoffset [ (毫秒精確度) ]datetimeoffset [ (fractional seconds precision) ]
使用方式Usage DECLARE @MyDatetimeoffset datetimeoffset(7)DECLARE @MyDatetimeoffset datetimeoffset(7)

CREATE TABLE Table1 ( Column1 datetimeoffset(7) )CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
預設的字串常值格式 (用於下層用戶端)Default string literal formats (used for down-level client) YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]

如需詳細資訊,請參閱下列的<下層用戶端的回溯相容性>一節。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.9999999 (Informatica 中不支援毫秒)00:00:00 through 23:59:59.9999999 (fractional seconds are not supported in Informatica)
時區位移範圍Time zone offset range -14:00 到 +14:00 (Informatica 中會忽略時區位移)-14:00 through +14:00 (the time zone offset is ignored in Informatica)
元素範圍Element ranges YYYY 是代表年份的四位數,範圍介於 0001 至 9999 之間。YYYY is four digits, ranging from 0001 through 9999, that represent a year.

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

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

hh 是代表小時的兩位數,範圍介於 00 至 23 之間。hh is two digits, ranging from 00 to 23, that represent the hour.

mm 是代表分鐘的兩位數,範圍介於 00 至 59 之間。mm is two digits, ranging from 00 to 59, that represent the minute.

ss 是代表秒鐘的兩位數,範圍介於 00 至 59 之間。ss is two digits, ranging from 00 to 59, that represent the second.

n* 是代表小數秒數的零至七位數,範圍介於 0 至 9999999 之間。n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. Informatica 中不支援毫秒。Fractional seconds are not supported in Informatica.

hh 是兩位數,範圍介於 -14 至 +14 之間。hh is two digits that range from -14 to +14. Informatica 中會忽略時區位移。The time zone offset is ignored in Informatica.

mm 是兩位數,範圍介於 00 至 59 之間。mm is two digits that range from 00 to 59. Informatica 中會忽略時區位移。The time zone offset is ignored in Informatica.
字元長度Character length 最小 26 個位置 (YYYY-MM-DD hh:mm:ss {+|-}hh:mm),最大 34 個位置 (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)26 positions minimum (YYYY-MM-DD hh:mm:ss {+|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)
有效位數,小數位數Precision, scale 請參閱下表。See the table below.
儲存體大小Storage size 10 個位元組 (固定) 是預設值,而且具有 100ns 小數秒數有效位數的預設值。10 bytes, fixed is the default with the default of 100ns fractional second precision.
精確度Accuracy 100 奈秒100 nanoseconds
預設值Default value 1900-01-01 00:00:00 00:001900-01-01 00:00:00 00:00
日曆Calendar 西曆Gregorian
使用者自訂的小數秒數有效位數User-defined fractional second precision Yes
時區位移感知和保留Time zone offset aware and preservation Yes
日光節約感知Daylight saving aware No
指定的小數位數Specified scale 結果 (有效位數,小數位數)Result (precision, scale) 資料行長度 (以位元組為單位)Column length (bytes) 小數秒數有效位數Fractional seconds precision
datetimeoffsetdatetimeoffset (34,7)(34,7) 1010 77
datetimeoffset(0)datetimeoffset(0) (26,0)(26,0) 88 0-20-2
datetimeoffset(1)datetimeoffset(1) (28,1)(28,1) 88 0-20-2
datetimeoffset(2)datetimeoffset(2) (29,2)(29,2) 88 0-20-2
datetimeoffset(3)datetimeoffset(3) (30,3)(30,3) 99 3-43-4
datetimeoffset(4)datetimeoffset(4) (31,4)(31,4) 99 3-43-4
datetimeoffset(5)datetimeoffset(5) (32,5)(32,5) 1010 5-75-7
datetimeoffset(6)datetimeoffset(6) (33,6)(33,6) 1010 5-75-7
datetimeoffset(7)datetimeoffset(7) (34,7)(34,7) 1010 5-75-7

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

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

ISO 8601ISO 8601 DescriptionDescription
YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm] 這兩種格式不受 SET LANGUAGE 和 SET DATEFORMAT 工作階段地區設定的影響。These two formats are not affected by the SET LANGUAGE and SET DATEFORMAT session locale settings. datetimeoffsetdatetime 部分之間不允許有空格。Spaces are not allowed between the datetimeoffset and the datetime parts.
YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC)YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC) 根據 ISO 定義,這種格式表示 datetime 部分應該以國際標準時間 (UTC) 來表示。This format by ISO definition indicates the datetime portion should be expressed in Coordinated Universal Time (UTC). 例如,1999-12-12 12:30:30.12345 -07:00 應該表示成 1999-12-12 19:30:30.12345Z。For example, 1999-12-12 12:30:30.12345 -07:00 should be represented as 1999-12-12 19:30:30.12345Z.

時區位移Time zone offset

時區位移指定 timedatetime 值從 UTC 算起的時區位移。A time zone offset specifies the zone offset from UTC for a time or datetime value. 時區位移可表示成 [+|-] hh:mm:The time zone offset can be represented as [+|-] hh:mm:

  • hh 是代表時區位移中時數的兩位數,範圍介於 00 至 14 之間。hh is two digits that range from 00 to 14 and represent the number of hours in the time zone offset.
  • mm 是代表時區位移中額外分鐘數的兩位數,範圍介於 00 至 59 之間。mm is two digits, ranging from 00 to 59, that represent the number of additional minutes in the time zone offset.
  • + (加號) 或 - (減號) 是時區時差的必要符號。+ (plus) or - (minus) is the mandatory sign for a time zone offset. 這會指出若要取得當地時間,則必須在 UTC 時間中加上或扣除時區位移。This indicates whether the time zone offset is added or subtracted from the UTC time to obtain the local time. 時區位移的有效範圍介於 -14:00 至 +14:00 之間。The valid range of time zone offset is from -14:00 to +14:00.

時區位移範圍會遵循 XSD 結構描述定義的 W3C XML 標準,而且稍微與 SQL 2003 標準定義 (12:59 至 +14:00) 不同。The time zone offset range follows the W3C XML standard for XSD schema definition and is slightly different from the SQL 2003 standard definition, 12:59 to +14:00.

選擇性的類型參數「毫秒精確度」 會指定秒鐘小數部分的位數。The optional type parameter fractional seconds precision specifies the number of digits for the fractional part of the seconds. 這個值可以是介於 0 至 7 (100 奈秒) 之間的整數。This value can be an integer with 0 to 7 (100 nanoseconds). 預設的「毫秒精確度」 為 100 奈秒 (秒鐘小數部分有七位數)。The default fractional seconds precision is 100ns (seven digits for the fractional part of the seconds).

這項資料會儲存於資料庫中,而且在伺服器中進行處理、比較、儲存和索引 (如同 UTC)。The data is stored in the database and processed, compared, sorted, and indexed in the server as in UTC. 時區位移將保留在資料庫中以便日後擷取。The time zone offset will be preserved in the database for retrieval.

指定的時區位移將假設為日光節約時間 (DST) 感知並針對 DST 期間的任何指定 datetime 進行調整。The given time zone offset will be assumed to be daylight saving time (DST) aware and adjusted for any given datetime that is in the DST period.

若為 datetimeoffset 類型,則 UTC 和當地 (永續或轉換的時區位移) datetime 值將在插入、更新、算數、轉換或指派作業期間進行驗證。For datetimeoffset type, both UTC and local (to the persistent or converted time zone offset) datetime value will be validated during insert, update, arithmetic, convert, or assign operations. 如果偵測到無效的 UTC 或當地 (永續或轉換的時區位移) datetime 值,將會引發無效值的錯誤。The detection of any invalid UTC or local (to the persistent or converted time zone offset) datetime value will raise an invalid value error. 例如,9999-12-31 10:10:00 在 UTC 中有效,但是在時區位移 +13:50 的當地時間中則會發生溢位。For example, 9999-12-31 10:10:00 is valid in UTC, but overflow in local time to the time zone offset +13:50.

若要將 date 轉換成在目標時區中對應的 datetimeoffset 值,請參閱 AT TIME ZONE (Transact-SQL)To convert a date to a corresponding datetimeoffset value in a target time zone, see AT TIME ZONE (Transact-SQL).

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

datetime 主題的<ANSI 和 ISO 8601 合規性>一節適用於 datetimeoffsetThe ANSI and ISO 8601 Compliance sections of the date and time topics apply to datetimeoffset.

下層用戶端的回溯相容性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)

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

本節說明當 datetimeoffset 資料類型轉換成其他日期和時間資料類型時,可能發生的狀況。This section describes what occurs when a datetimeoffset data type is converted to other date and time data types.

當轉換成 date 時,年、月和日都會複製。When converting to date, the year, month, and day are copied. 下列程式碼顯示將 datetimeoffset(4) 值轉換成 date 值的結果。The following code shows the results of converting a datetimeoffset(4) value to a date value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';  
DECLARE @date date= @datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';  
  
--Result  
--@datetimeoffset                date  
-------------------------------- ----------  
--2025-12-10 12:32:10.0000 +01:0 2025-12-10  
--  
--(1 row(s) affected)  
  

如果轉換成 time(n) ,時、分、秒和毫秒都會複製。If the conversion is to time(n), the hour, minute, second, and fractional seconds are copied. 時區值則會被截斷。The time zone value is truncated. 如果 datetimeoffset(n) 值的有效位數大於 time(n) 值的有效位數,此值將會四捨五入。When the precision of the datetimeoffset(n) value is greater than the precision of the time(n) value, the value is rounded up. 下列程式碼顯示將 datetimeoffset(4) 值轉換成 time(3) 值的結果。The following code shows the results of converting a datetimeoffset(4) value to a time(3) value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10.1237 +01:0';  
DECLARE @time time(3) = @datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';  
  
--Result  
--@datetimeoffset                time  
-------------------------------- ------------  
-- 2025-12-10 12:32:10.1237 +01:00    12:32:10.124  
  
--  
--(1 row(s) affected)  
  

當轉換成 datetime 時,日期和時間值都會複製,而時區則會被截斷。When converting todatetime, the date and time values are copied, and the time zone is truncated. 如果 datetimeoffset(n) 值的小數有效位數大於三位數,此值將會被截斷。When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value is truncated. 下列程式碼顯示將 datetimeoffset(4) 值轉換成 datetime 值的結果。The following code shows the results of converting a datetimeoffset(4) value to a datetime value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10.1237 +01:0';  
DECLARE @datetime datetime = @datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';  
  
--Result  
--@datetimeoffset                datetime  
-------------------------------- -----------------------  
--2025-12-10 12:32:10.1237 +01:0 2025-12-10 12:32:10.123  
--  
--(1 row(s) affected)  

當轉換成 smalldatetime 時,會複製日期和小時。For conversions to smalldatetime, the date and hours are copied. 分鐘會根據秒值四捨五入,而秒會設定為 0。The minutes are rounded up with respect to the seconds value and seconds are set to 0. 下列程式碼顯示將 datetimeoffset(3) 值轉換成 smalldatetime 值的結果。The following code shows the results of converting a datetimeoffset(3) value to a smalldatetime value.

DECLARE @datetimeoffset datetimeoffset(3) = '1912-10-25 12:24:32 +10:0';  
DECLARE @smalldatetime smalldatetime = @datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';  
  
--Result  
--@datetimeoffset                @smalldatetime  
-------------------------------- -----------------------  
--1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00  
--  
--(1 row(s) affected)  

當轉換成 datetime2(n) 時,日期和時間會複製到 datetime2 值,而時區則會被截斷。If the conversion is to datetime2(n), the date and time are copied to the datetime2 value, and the time zone is truncated. 如果 datetime2(n) 值的有效位數大於 datetimeoffset(n) 值的有效位數,小數秒數會被截斷以配合其大小。When the precision of the datetime2(n) value is greater than the precision of the datetimeoffset(n) value, the fractional seconds are truncated to fit. 下列程式碼顯示將 datetimeoffset(4) 值轉換成 datetime2(3) 值的結果。The following code shows the results of converting a datetimeoffset(4) value to a datetime2(3) value.

DECLARE @datetimeoffset datetimeoffset(4) = '1912-10-25 12:24:32.1277 +10:0';  
DECLARE @datetime2 datetime2(3)=@datetimeoffset;  
  
SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';  
  
--Result  
@datetimeoffset                    @datetime2  
---------------------------------- ----------------------  
1912-10-25 12:24:32.1277 +10:00    1912-10-25 12:24:32.12  
  
--(1 row(s) affected)  

將字串常值轉換為 datetimeoffsetConverting string literals to datetimeoffset

如果整個字串皆是有效的格式,即可從字串常值轉換為日期與時間類型。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. 下表是字串常值轉換為 datetimeoffset 資料類型的規則。The following table shows the rules for converting a string literal to the datetimeoffset data type.

輸入字串常值Input string literal datetimeoffset(n)datetimeoffset(n)
ODBC DATEODBC DATE ODBC 字串常值會對應到 datetime 資料類型。ODBC string literals are mapped to the datetime data type. 任何將 ODBC DATETIME 常值指派成 datetimeoffset 類型的指派作業,皆會根據轉換規則,執行 datetime 與此類型之間的隱含轉換。Any assignment operation from ODBC DATETIME literals into datetimeoffset 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. TIMEZONE 預設為 +00:00。The TIMEZONE defaults to +00:00.
僅限 TIMETIME only DATE 部分預設為 1900-1-1。The DATE part defaults to 1900-1-1. TIMEZONE 將預設為 +00:00。The TIMEZONE will default to +00:00.
僅限 TIMEZONETIMEZONE only 提供預設值Default values are supplied
DATE + TIMEDATE + TIME TIMEZONE 預設為 +00:00。The TIMEZONE defaults to +00:00.
DATE + TIMEZONEDATE + TIMEZONE 不允許Not allowed
TIME + TIMEZONETIME + TIMEZONE DATE 部分預設為 1900-1-1。The DATE part defaults to 1900-1-1.
DATE + TIME + TIMEZONEDATE + TIME + TIMEZONE 一般Trivial

範例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'  
    ,CAST('2007-05-08 12:35:29.1234567+12:15' AS datetimeoffset(7)) AS  
        'datetimeoffset IS08601';  

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

資料類型Data type 輸出Output
TimeTime 12:35:29.12:35:29. 12345671234567
日期Date 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)
AT TIME ZONE (Transact-SQL)AT TIME ZONE (Transact-SQL)