時間 (Transact-SQL)time (Transact-SQL)

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

定義一天的時間。Defines a time of a day. 這個時間不含時區感知,而且是以 24 小時制為基礎。The time is without time zone awareness and is based on a 24-hour clock.

注意

Informatica 資訊會提供給使用 Informatica Connector 的 PDW 客戶。Informatica information is provided for PDW customers using the Informatica Connector.

time 描述time Description

屬性Property ReplTest1Value
語法Syntax time [ (毫秒小數位數) ]time [ (fractional second scale) ]
使用方式Usage DECLARE @MyTime time(7)DECLARE @MyTime time(7)

CREATE TABLE Table1 ( Column1 time(7) )CREATE TABLE Table1 ( Column1 time(7) )
毫秒小數位數fractional seconds scale 指定秒鐘小數部分的位數。Specifies the number of digits for the fractional part of the seconds.

這可以是介於 0 至 7 之間的整數。This can be an integer from 0 to 7. 針對 Informatica,這可以是介於 0 至 3 之間的整數。For Informatica, this can be an integer from 0 to 3.

預設的小數位數是 7 (100 毫秒)。The default fractional scale is 7 (100ns).
預設的字串常值格式Default string literal format

(用於下層用戶端)(used for down-level client)
Informatica 為 hh:mm:ss[.nnnnnnn])hh:mm:ss[.nnnnnnn] for Informatica)

如需詳細資訊,請參閱下層用戶端的回溯相容性一節。For more information, see the Backward Compatibility for Down-level Clients section.
範圍Range 00:00:00.0000000 到 23:59:59.9999999 (Informatica 為 00:00:00.000 到 23:59:59.999)00:00:00.0000000 through 23:59:59.9999999 (00:00:00.000 through 23:59:59.999 for Informatica)
元素範圍Element ranges hh 是代表小時的兩位數,範圍介於 0 至 23 之間。hh is two digits, ranging from 0 to 23, that represent the hour.

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

ss 是代表秒鐘的兩位數,範圍介於 0 至 59 之間。ss is two digits, ranging from 0 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,n* 為 0 到 3 位數,範圍為 0 至 999。For Informatica, n* is zero to three digits, ranging from 0 to 999.
字元長度Character length 最小 8 個位置 (hh:mm:ss),最大 16 個位置 (hh:mm:ss.nnnnnnn)。8 positions minimum (hh:mm:ss) to 16 maximum (hh:mm:ss.nnnnnnn). 針對 Informatica,最大值為 12 (hh:mm:ss.nnn)。For Informatica, the maximum is 12 (hh:mm:ss.nnn).
有效位數,小數位數Precision, scale

(使用者僅指定小數位數)(user specifies scale only)
請參閱下表。See the table below.
儲存體大小Storage size 固定的 5 個位元組是具有預設 100ns 小數秒數有效位數的預設值。5 bytes, fixed, is the default with the default of 100ns fractional second precision. 在 Informatica 中,預設為 4 個位元組、固定,並具有預設 1ms 毫秒精確度。In Informatica, the default is 4 bytes, fixed, with the default of 1ms fractional second precision.
精確度Accuracy 100 奈秒 (Informatica 中為 1 毫秒)100 nanoseconds (1 millisecond in Informatica)
預設值Default value 00:00:0000:00:00

這個值會用於從 date 隱含轉換成 datetime2datetimeoffset 的附加時間部分。This value is used for the appended time part for implicit conversion from date to datetime2 or datetimeoffset.
使用者自訂的小數秒數有效位數User-defined fractional second precision Yes
時區位移感知和保留Time zone offset aware and preservation No
日光節約感知Daylight saving aware No
指定的小數位數Specified scale 結果 (有效位數,小數位數)Result (precision, scale) 資料行長度 (以位元組為單位)Column length (bytes) 小數Fractional

secondsseconds

有效位數precision
timetime (16,7) [Informatica 中為 (12,3)](16,7) [(12,3) in Informatica] 5 (Informatica 中為 4)5 (4 in Informatica) 7 (Informatica 中為 3)7 (3 in Informatica)
time(0)time(0) (8,0)(8,0) 33 0-20-2
time(1)time(1) (10,1)(10,1) 33 0-20-2
time(2)time(2) (11,2)(11,2) 33 0-20-2
time(3)time(3) (12,3)(12,3) 44 3-43-4
time(4)time(4)

Informatica 中不支援此項目。Not supported in Informatica.
(13,4)(13,4) 44 3-43-4
time(5)time(5)

Informatica 中不支援此項目。Not supported in Informatica.
(14,5)(14,5) 55 5-75-7
time(6)time(6)

Informatica 中不支援此項目。Not supported in Informatica.
(15,6)(15,6) 55 5-75-7
time(7)time(7)

Informatica 中不支援此項目。Not supported in Informatica.
(16,7)(16,7) 55 5-75-7

支援 time 的字串常值格式Supported String Literal Formats for time

下表顯示 time 資料類型的有效字串常值格式。The following table shows the valid string literal formats for the time data type.

SQL ServerSQL Server DescriptionDescription
hh:mm[:ss][:小數秒數][AM][PM]hh:mm[:ss][:fractional seconds][AM][PM]

hh:mm[:ss][.小數秒數][AM][PM]hh:mm[:ss][.fractional seconds][AM][PM]

hhAM[PM]hhAM[PM]

hh AM[PM]hh AM[PM]
無論您是否指定 AM,只要小時的值為 0 就表示午夜之後 (AM) 的小時。The hour value of 0 represents the hour after midnight (AM), regardless of whether AM is specified. 小時等於 0 時,無法指定 PM。PM cannot be specified when the hour equals 0.

如果沒有指定 AM 或 PM,小時值 01 至 11 就代表正午之前的小時。Hour values from 01 through 11 represent the hours before noon if neither AM nor PM is specified. 指定為 AM 時,這些值就代表正午之前的小時。The values represent the hours before noon when AM is specified. 如果指定為 PM,這些值就代表正午之後的小時。The values represent hours after noon if PM is specified.

如果沒有指定 AM 或 PM,小時值 12 就代表從正午開始的小時。The hour value 12 represents the hour that starts at noon if neither AM nor PM is specified. 如果指定為 AM,此值就代表從午夜開始的小時。If AM is specified, the value represents the hour that starts at midnight. 如果指定為 PM,此值就代表從正午開始的小時。If PM is specified, the value represents the hour that starts at noon. 例如,12:01 就是正午之後的 1 分鐘,也就是 12:01 PM,而 12:01 AM 則是午夜之後一分鐘。For example, 12:01 is 1 minute after noon, as is 12:01 PM; and 12:01 AM is one minute after midnight. 指定為 12:01 AM 與指定為 00:01 或 00:01 AM 是相同的。Specifying 12:01 AM is the same as specifying 00:01 or 00:01 AM.

如果未指定 AM 或 PM,從 13 到 23 的小時值就代表正午之後的小時。Hour values from 13 through 23 represent hours after noon if AM or PM is not specified. 如果指定為 PM,這些值也代表正午之後的小時。The values also represent the hours after noon when PM is specified. 當小時值為 13 至 23 時,則無法指定為 AM。AM cannot be specified when the hour value is from 13 through 23.

24 小時值無效。An hour value of 24 is not valid. 若要表示午夜,請使用 12: 00 AM 或 00:00。To represent midnight, use 12:00 AM or 00:00.

毫秒前可以用冒號 (:) 或句號 (.)。Milliseconds can be preceded by either a colon (:) or a period (.). 如果使用冒號,數字是指千分之一秒。If a colon is used, the number means thousandths-of-a-second. 如果使用句號,一位數代表十分之一秒、二位數代表百分之一秒,而三位數代表千分之一秒。If a period is used, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. 例如,12:30:20:1 表示 12:30 過 20 又千分之一秒;12:30:20.1 表示 12:30 過 20 又十分之一秒。For example, 12:30:20:1 indicates 20 and one-thousandth seconds past 12:30; 12:30:20.1 indicates 20 and one-tenth seconds past 12:30.
ISO 8601ISO 8601 注意Notes
hh:mm:sshh:mm:ss

hh:mm[:ss][.小數秒數]hh:mm[:ss][.fractional seconds]
hh 表示時區時差中的兩位數時數,範圍介於 0 至 23 之間。hh is two digits, ranging from 0 to 23, that represent the number of hours in the time zone offset.

mm 是代表時區位移中額外分鐘數的兩位數,範圍介於 0 至 59 之間。mm is two digits, ranging from 0 to 59, that represent the number of additional minutes in the time zone offset.
ODBCODBC 注意Notes
{t 'hh:mm:ss[.小數秒數]'}{t 'hh:mm:ss[.fractional seconds]'} ODBC API 專用。ODBC API specific.

符合 ANSI 和 ISO 8601 標準Compliance with ANSI and ISO 8601 Standards

為了達成回溯相容並與現有日期和時間類型一致,因此不支援使用 24 小時制來代表午夜和超過 59 的閏秒 (如 ISO 8601 (5.3.2 和 5.3) 所定義)。Using hour 24 to represent midnight and leap second over 59 as defined by ISO 8601 (5.3.2 and 5.3) are not supported to be backward compatible and consistent with the existing date and time types.

預設字串常值格式 (用於下層用戶端) 將會符合 SQL 標準格式 (定義為 hh:mm:ss[.nnnnnnn])。The default string literal format (used for down-level client) will align with the SQL standard form, which is defined as hh:mm:ss[.nnnnnnn]. 這種格式與 TIME 的 ISO 8601 定義很相似 (不含小數秒數)。This format resembles the ISO 8601 definition for TIME excluding fractional seconds.

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

將 time(n) 資料類型轉換成其他日期與時間類型Converting time(n) Data Type to Other Date and Time Types

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

當轉換成 time(n) 時,時、分和秒都會複製。When the conversion is to time(n), the hour, minute, and seconds are copied. 當目標有效位數小於來源有效位數時,毫秒將會四捨五入,以配合目標有效位數。When the destination precision is less than the source precision, the fractional seconds is rounded up to fit the destination precision. 下列範例顯示將 time(4) 值轉換成 time(3) 值的結果。The following example shows the results of converting a time(4) value to a time(3) value.

DECLARE @timeFrom time(4) = '12:34:54.1237';  
DECLARE @timeTo time(3) = @timeFrom;  
  
SELECT @timeTo AS 'time(3)', @timeFrom AS 'time(4)';  
  
--Results  
--time(3)      time(4)  
-------------- -------------  
--12:34:54.124 12:34:54.1237  
--  
--(1 row(s) affected)  

如果轉換目標為 date,轉換會失敗,並引發錯誤訊息 206:「運算元類型衝突:date 與 time 不相容」。If the conversion is to date, the conversion fails, and error message 206 is raised: "Operand type clash: date is incompatible with time".

當轉換成 datetime 時,將會複製時、分和秒的值,而且日期元件會設定為 '1900-01-01'。When the conversion is to datetime, hour, minute, and second values are copied; and the date component is set to '1900-01-01'. 如果 time(n) 值的毫秒精確度大於三位數,datetime 結果將會被截斷。When the fractional seconds precision of the time(n) value is greater than three digits, the datetime result will be truncated. 下列程式碼顯示將 time(4) 值轉換成 datetime 值的結果。The following code shows the results of converting a time(4) value to a datetime value.

DECLARE @time time(4) = '12:15:04.1237';  
DECLARE @datetime datetime= @time;  
SELECT @time AS '@time', @datetime AS '@datetime';  
  
--Result  
--@time         @datetime  
--------------- -----------------------  
--12:15:04.1237 1900-01-01 12:15:04.123  
--  
--(1 row(s) affected)  
  

當轉換成 smalldatetime 時,日期會設定為 '1900-01-01',而小時和分鐘值會四捨五入。When the conversion is to smalldatetime, the date is set to '1900-01-01', and the hour and minute values are rounded up. 秒和小數秒數會設定為 0。The seconds and fractional seconds are set to 0. 下列程式碼顯示將 time(4) 值轉換成 smalldatetime 值的結果。The following code shows the results of converting a time(4) value to a smalldatetime value.

-- Shows rounding up of the minute value.  
DECLARE @time time(4) = '12:15:59.9999';   
DECLARE @smalldatetime smalldatetime= @time;    
SELECT @time AS '@time', @smalldatetime AS '@smalldatetime';   
  
--Result  
@time            @smalldatetime  
---------------- -----------------------  
12:15:59.9999    1900-01-01 12:16:00--  
--(1 row(s) affected)  
  
-- Shows rounding up of the hour value.  
DECLARE @time time(4) = '12:59:59.9999';   
DECLARE @smalldatetime smalldatetime= @time;    
  
SELECT @time AS '@time', @smalldatetime AS '@smalldatetime';  
@time            @smalldatetime  
---------------- -----------------------  
12:59:59.9999    1900-01-01 13:00:00  
  
(1 row(s) affected)  
  

當轉換成 datetimeoffset(n) 時,日期會設定為 '1900-01-01',時間則會複製。If the conversion is to datetimeoffset(n), the date is set to '1900-01-01', and the time is copied. 時區時差會設定為 +00:00。The time zone offset is set to +00:00. 如果 time(n) 值的毫秒精確度大於 datetimeoffset(n) 值的有效位數,此值會四捨五入以配合其大小。When the fractional seconds precision of the time(n) value is greater than the precision of the datetimeoffset(n) value, the value is rounded up to fit. 下列範例顯示將 time(4) 值轉換成 datetimeoffset(3) 類型的結果。The following example shows the results of converting a time(4) value to a datetimeoffset(3) type.

DECLARE @time time(4) = '12:15:04.1237';  
DECLARE @datetimeoffset datetimeoffset(3) = @time;  
  
SELECT @time AS '@time', @datetimeoffset AS '@datetimeoffset';  
  
--Result  
--@time         @datetimeoffset  
--------------- ------------------------------  
--12:15:04.1237 1900-01-01 12:15:04.124 +00:00  
--  
--(1 row(s) affected)  
  

當轉換成 datetime2(n) 時,日期會設定為 '1900-01-01' 且會複製時間元件,而時區位移會設定為 00:00。When converting to datetime2(n), the date is set to '1900-01-01', the time component is copied, and the time zone offset is set to 00:00. 如果 datetime2(n) 值的毫秒精確度大於 time(n) 值,此值將會四捨五入以配合其大小。When the fractional seconds precision of the datetime2(n) value is greater than the time(n) value, the value will be rounded up to fit. 下列範例顯示將 time(4) 值轉換成 datetime2(2) 值的結果。The following example shows the results of converting a time(4) value to a datetime2(2) value.

DECLARE @time time(4) = '12:15:04.1237';  
DECLARE @datetime2 datetime2(3) = @time;  
  
SELECT @datetime2 AS '@datetime2', @time AS '@time';  
  
--Result  
--@datetime2              @time  
------------------------- -------------  
--1900-01-01 12:15:04.124 12:15:04.1237  
--  
--(1 row(s) affected)  

將字串常值轉換為 time(n)Converting String Literals to time(n)

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

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

範例Examples

A.A. 比較 date 和 time 資料類型Comparing date and time Data Types

下列範例會比較將字串轉換成各種 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';  
資料類型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

B.B. 將有效的時間字串常值插入 time(7) 資料行Inserting Valid Time String Literals into a time(7) Column

下表將列出可插入 time(7) 資料類型之資料行的不同字串常值,以及之後儲存在該資料行中的值。The following table lists different string literals that can be inserted into a column of data type time(7) with the values that are then stored in that column.

字串常值格式類型String literal format type 插入的字串常值Inserted string literal 儲存的 time(7) 值time(7) value that is stored DescriptionDescription
SQL ServerSQL Server '01:01:01:123AM''01:01:01:123AM' 01:01:01.123000001:01:01.1230000 當冒號 (:) 出現在小數秒數有效位數之前時,小數位數就無法超過三個位置,否則將會引發錯誤。When a colon (:) comes before fractional seconds precision, scale cannot exceed three positions or an error will be raised.
SQL ServerSQL Server '01:01:01.1234567 AM''01:01:01.1234567 AM' 01:01:01.123456701:01:01.1234567 指定為 AM 或 PM 時,時間就會採用 24 小時格式儲存,但不含常值 AM 或 PM。When AM or PM is specified, the time is stored in 24-hour format without the literal AM or PM
SQL ServerSQL Server '01:01:01.1234567 PM''01:01:01.1234567 PM' 13:01:01.123456713:01:01.1234567 指定為 AM 或 PM 時,時間就會採用 24 小時格式儲存,但不含常值 AM 或 PM。When AM or PM is specified, the time is stored in 24-hour format without the literal AM or PM
SQL ServerSQL Server '01:01:01.1234567PM''01:01:01.1234567PM' 13:01:01.123456713:01:01.1234567 AM 或 PM 之前的空格是選擇性的。A space before AM or PM is optional.
SQL ServerSQL Server '01AM''01AM' 01:00:00.000000001:00:00.0000000 如果只有指定小時,則所有其他值都是 0。When only the hour is specified, all other values are 0.
SQL ServerSQL Server '01 AM''01 AM' 01:00:00.000000001:00:00.0000000 AM 或 PM 之前的空格是選擇性的。A space before AM or PM is optional.
SQL ServerSQL Server '01:01:01''01:01:01' 01:01:01.000000001:01:01.0000000 沒有指定小數秒數有效位數時,資料類型所定義的每個位置都是 0。When fractional seconds precision is not specified, each position that is defined by the data type is 0.
ISO 8601ISO 8601 '01:01:01.1234567''01:01:01.1234567' 01:01:01.123456701:01:01.1234567 若要符合 ISO 8601,請使用 24 小時格式,而非 AM 或 PM。To comply with ISO 8601, use 24-hour format, not AM or PM.
ISO 8601ISO 8601 '01:01:01.1234567 +01:01''01:01:01.1234567 +01:01' 01:01:01.123456701:01:01.1234567 雖然輸入中允許使用選擇性的時區差異 (TZD),但是不會進行儲存。The optional time zone difference (TZD) is allowed in the input but is not stored.

C.C. 將時間字串常值插入每個 date 和 time 資料類型的資料行Inserting Time String Literal into Columns of Each date and time Date Type

在下表中,第一個資料行會顯示即將插入 date 或 time 資料類型 (顯示於第二個資料行) 之資料庫資料表資料行的時間字串常值。In the following table the first column shows a time string literal to be inserted into a database table column of the date or time data type shown in the second column. 第三個資料行會顯示將儲存在資料庫資料表資料行中的值。The third column shows the value that will be stored in the database table column.

插入的字串常值Inserted string literal 資料行資料類型Column data type 儲存在資料行中的值Value that is stored in column DescriptionDescription
'12:12:12.1234567''12:12:12.1234567' time(7)time(7) 12:12:12.123456712:12:12.1234567 如果小數秒數有效位數超過針對資料行所指定的值,字串就會被截斷,但不會發生錯誤。If the fractional seconds precision exceeds the value specified for the column, the string will be truncated without error.
'2007-05-07''2007-05-07' datedate NULLNULL 任何時間值都會導致 INSERT 陳述式失敗。Any time value will cause the INSERT statement to fail.
'12:12:12''12:12:12' smalldatetimesmalldatetime 1900-01-01 12:12:001900-01-01 12:12:00 任何小數秒數有效位數值都會導致 INSERT 陳述式失敗。Any fractional seconds precision value will cause the INSERT statement to fail.
'12:12:12.123''12:12:12.123' datetimedatetime 1900-01-01 12:12:12.1231900-01-01 12:12:12.123 任何長度超過三個位置的秒數有效位數都會導致 INSERT 陳述式失敗。Any second precision longer than three positions will cause the INSERT statement to fail.
'12:12:12.1234567''12:12:12.1234567' datetime2(7)datetime2(7) 1900-01-01 12:12:12.12345671900-01-01 12:12:12.1234567 如果小數秒數有效位數超過針對資料行所指定的值,字串就會被截斷,但不會發生錯誤。If the fractional seconds precision exceeds the value specified for the column, the string will be truncated without error.
'12:12:12.1234567''12:12:12.1234567' datetimeoffset(7)datetimeoffset(7) 1900-01-01 12:12:12.1234567 +00:001900-01-01 12:12:12.1234567 +00:00 如果小數秒數有效位數超過針對資料行所指定的值,字串就會被截斷,但不會發生錯誤。If the fractional seconds precision exceeds the value specified for the column, the string will be truncated without error.

另請參閱See Also

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