datetime (Transact-SQL)datetime (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 day with fractional seconds that is based on a 24-hour clock.

注意

對新工作使用 timedatedatetime2datetimeoffset 資料類型。Use the time, date, datetime2 and datetimeoffset data types for new work. 這些類型符合 SQL 標準。These types align with the SQL Standard. 它們具有方便移植的特性。They are more portable. timedatetime2datetimeoffset 提供更多秒數有效位數。time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset 可為全域部署的應用程式提供時區支援。datetimeoffset provides time zone support for globally deployed applications.

datetime 描述datetime Description

屬性Property ReplTest1Value
語法Syntax datetimedatetime
使用方式Usage DECLARE @MyDatetime datetimeDECLARE @MyDatetime datetime

建立資料表 Table1 (Column1 datetime )CREATE TABLE Table1 ( Column1 datetime )
預設的字串常值格式Default string literal formats

(用於下層用戶端)(used for down-level client)
不適用Not applicable
日期範圍Date range 1753 年 1 月 1 日到 9999 年 12 月 31 日January 1, 1753, through December 31, 9999
時間範圍Time range 00:00:00 到 23:59:59.99700:00:00 through 23:59:59.997
時區位移範圍Time zone offset range NoneNone
元素範圍Element ranges YYYY 是代表年份的四位數,範圍介於 1753 至 9999 之間。YYYY is four digits from 1753 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 至 999 之間。n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.
字元長度Character length 最小 19 個位置,最大 23 個位置19 positions minimum to 23 maximum
儲存體大小Storage size 8 個位元組8 bytes
精確度Accuracy 四捨五入成 .000、.003 或 .007 秒的遞增。Rounded to increments of .000, .003, or .007 seconds
預設值Default value 1900-01-01 00:00:001900-01-01 00:00:00
日曆Calendar 西曆 (包含年份的完整範圍)。Gregorian (Does include the complete range of years.)
使用者自訂的小數秒數有效位數User-defined fractional second precision No
時區位移感知和保留Time zone offset aware and preservation No
日光節約感知Daylight saving aware No

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

下表列出 datetime 支援的字串常值格式。The following tables list the supported string literal formats for datetime. 除了 ODBC 以外,datetime 字串常值都位於單引號 (') 中,例如 'string_literaL'。Except for ODBC, datetime string literals are in single quotation marks ('), for example, 'string_literaL'. 如果環境不是 us_english,字串常值就必須採用 N'string_literaL' 格式。If the environment isn't us_english, the string literals should be in the format N'string_literaL'.

數值Numeric DescriptionDescription
日期格式:Date formats:

[0]4/15/[19]96 -- (mdy)[0]4/15/[19]96 -- (mdy)

[0]4-15-[19]96 -- (mdy)[0]4-15-[19]96 -- (mdy)

[0]4.15.[19]96 -- (mdy)[0]4.15.[19]96 -- (mdy)

[0]4/[19]96/15 -- (myd)[0]4/[19]96/15 -- (myd)

15/[0]4/[19]96 -- (dmy)15/[0]4/[19]96 -- (dmy)

15/[19]96/[0]4 -- (dym)15/[19]96/[0]4 -- (dym)

[19]96/15/[0]4 -- (ydm)[19]96/15/[0]4 -- (ydm)

[19]96/[0]4/15 -- (ymd)[19]96/[0]4/15 -- (ymd)

時間格式:Time formats:

14:3014:30

14:30[:20:999]14:30[:20:999]

14:30[:20.9]14:30[:20.9]

4am4am

4 PM4 PM
您可以使用指定的數值月份來指定日期資料。You can specify date data with a numeric month specified. 例如,5/20/97 代表 1997 年 5 月 20 日。For example, 5/20/97 represents the twentieth day of May 1997. 當您使用數值日期格式時,請在使用斜線 (/)、連字號 (-) 或句號 (.) 做為分隔符號的字串中指定月、日和年。When you use numeric date format, specify the month, day, and year in a string that uses slash marks (/), hyphens (-), or periods (.) as separators. 此字串必須以下列形式出現:This string must appear in the following form:

數字分隔符號數字分隔符號數字 [time] [time]number separator number separator number [time] [time]



將語言設成 us_english 時,日期的預設順序是 mdy。When the language is set to us_english, the default order for the date is mdy. 您可以使用 SET DATEFORMAT 陳述式來變更日期順序。You can change the date order by using the SET DATEFORMAT statement.

SET DATEFORMAT 陳述式的設定會影響日期值的解譯方式。The setting for SET DATEFORMAT determines how date values are interpreted. 如果順序不符合設定,值就不會解譯為日期。If the order doesn't match the setting, the values aren't interpreted as dates. 不符合規則的日期可能會誤解為超出範圍,或使用錯誤的值。Out-of-order dates may be misinterpreted as out of range or with wrong values. 例如,依 DATEFORMAT 設定而定,可以將 12/10/08 解譯為六種日期之一。For example, 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting. 四部分的年份會解譯為年份。A four-part year is interpreted as the year.
字母順序Alphabetical DescriptionDescription
Apr[il] [15][,] 1996Apr[il] [15][,] 1996

Apr[il] 15[,] [19]96Apr[il] 15[,] [19]96

Apr[il] 1996 [15]Apr[il] 1996 [15]

[15] Apr[il][,] 1996[15] Apr[il][,] 1996

15 Apr[il][,][19]9615 Apr[il][,][19]96

15 [19]96 apr[il]15 [19]96 apr[il]

[15] 1996 apr[il][15] 1996 apr[il]

1996 APR[IL] [15]1996 APR[IL] [15]

1996 [15] APR[IL]1996 [15] APR[IL]
您可以使用指定為完整月份名稱的月份來指定日期資料。You can specify date data with a month specified as the full month name. 例如,April 或該月份在目前語言中指定的縮寫 Apr。逗號是選擇性且會忽略大小寫。For example, April or the month abbreviation of Apr specified in the current language; commas are optional and capitalization is ignored.

以下是使用字母日期格式的一些指導方針:Here are some guidelines for using alphabetical date formats:

1) 將日期和時間資料括在單引號 (') 中。1) Enclose the date and time data in single quotation marks ('). 若為英文以外的語言,請使用 N'。For languages other than English, use N'

2) 以括號括住的字元是選擇性的。2) Characters that are enclosed in brackets are optional.

3) 如果只指定年份的後兩位數字,這些數值小於設定 two digit year cutoff 伺服器組態選項組態選項的後兩位數字時,就與截斷年份同一世紀。3) If you specify only the last two digits of the year, values less than the last two digits of the value of the Configure the two digit year cutoff Server Configuration Option configuration option are in the same century as the cutoff year. 數值如果大於或等於這個選項的值,就在截止年份的前一個世紀。Values greater than or equal to the value of this option are in the century that comes before the cutoff year. 例如,如果 two digit year cutoff 是 2050 (預設值),25 就會解譯為 2025,而 50 則解譯為 1950。For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. 若要避免模糊不清,請使用四位數年份。To avoid ambiguity, use four-digit years.

4) 如果漏了日的部分,就用當月第一天。4) If the day is missing, the first day of the month is supplied.



以字母形式指定月份時,就不適用 SET DATEFORMAT 工作階段設定。The SET DATEFORMAT session setting isn't applied when you specify the month in alphabetical form.
ISO 8601ISO 8601 DescriptionDescription
YYYY-MM-DDThh:mm:ss[.mmm]YYYY-MM-DDThh:mm:ss[.mmm]

YYYYMMDD[ hh:mm:ss[.mmm]]YYYYMMDD[ hh:mm:ss[.mmm]]
範例:Examples:

1) 2004-05-23T14:25:101) 2004-05-23T14:25:10

2) 2004-05-23T14:25:10.4872) 2004-05-23T14:25:10.487



若要使用 ISO 8601 格式,您必須指定格式的每個項目,包括出現在格式中的 T、冒號 (:) 和句號 (.)。To use the ISO 8601 format, you must specify each element in the format, including the T, the colons (:), and the period (.) that are shown in the format.

括號指出秒數部分的小數是選擇性的。The brackets indicate that the fraction of second component is optional. 時間元件會使用 24 小時制格式來指定。The time component is specified in the 24-hour format.

T 指出 datetime 值之時間部份的開始。The T indicates the start of the time part of the datetime value.

使用 ISO 8601 格式的優點在於它是國際標準,而且沒有模糊不清的規格。The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. 另外,此格式不受 SET DATEFORMAT 或 SET LANGUAGE 設定影響。Also, this format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.
未分隔Unseparated DescriptionDescription
YYYYMMDD hh:mm:ss[.mmm]YYYYMMDD hh:mm:ss[.mmm]
ODBCODBC DescriptionDescription
{ ts '1998-05-02 01:23:56.123' }{ ts '1998-05-02 01:23:56.123' }

{ d '1990-10-02' }{ d '1990-10-02' }

{ t '13:33:41' }{ t '13:33:41' }
ODBC API 定義了逸出序列來代表日期和時間值,供 ODBC 呼叫時間戳記資料。The ODBC API defines escape sequences to represent date and time values, which ODBC calls timestamp data. SQL ServerSQL ServerMicrosoftMicrosoft OLE DB 提供者所支援的 OLE DB 語言定義 (DBGUID-SQL) 也支援 ODBC 時間戳記格式。This ODBC timestamp format is also supported by the OLE DB language definition (DBGUID-SQL) supported by the MicrosoftMicrosoft OLE DB provider for SQL ServerSQL Server. 使用 ADO、OLE DB 與 ODBC 架構 API 的應用程式都可以使用這個 ODBC 時間戳記格式來代表日期和時間值。Applications that use the ADO, OLE DB, and ODBC-based APIs can use this ODBC timestamp format to represent dates and times.

ODBC 時間戳記逸出序列的格式如下:{ literal_type 'constant_value' }:ODBC timestamp escape sequences are of the format: { literal_type 'constant_value' }:



- literal_type 會指定逸出序列的類型。- literal_type specifies the type of the escape sequence. 時間戳記有三個 literal_type 規範:Timestamps have three literal_type specifiers:
1) d = 只有日期1) d = date only
2) t = 只有時間2) t = time only
3) ts = 時間戳記 (時間 + 日期)3) ts = timestamp (time + date)



- 'constant_value' 是逸出序列的值。- 'constant_value' is the value of the escape sequence. 對於各個 literal_typeconstant_value 都必須遵循這些格式。constant_value must follow these formats for each literal_type.
d:yyyy-mm-ddd : yyyy-mm-dd
t:hh:mm:ss[.fff]t : hh:mm:ss[.fff]
ts:yyyy-mm-dd hh:mm:ss[.fff]ts : yyyy-mm-dd hh:mm:ss[.fff]

datetime 小數秒數有效位數的四捨五入Rounding of datetime Fractional Second Precision

datetime 值會捨入到 .000、.003 或 .007 秒的遞增,如下表所示。datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

使用者指定的值User-specified value 系統預存值System stored value
01/01/98 23:59:59.99901/01/98 23:59:59.999 1998-01-02 00:00:00.0001998-01-02 00:00:00.000
01/01/98 23:59:59.99501/01/98 23:59:59.995

01/01/98 23:59:59.99601/01/98 23:59:59.996

01/01/98 23:59:59.99701/01/98 23:59:59.997

01/01/98 23:59:59.99801/01/98 23:59:59.998
1998-01-01 23:59:59.9971998-01-01 23:59:59.997
01/01/98 23:59:59.99201/01/98 23:59:59.992

01/01/98 23:59:59.99301/01/98 23:59:59.993

01/01/98 23:59:59.99401/01/98 23:59:59.994
1998-01-01 23:59:59.9931998-01-01 23:59:59.993
01/01/98 23:59:59.99001/01/98 23:59:59.990

01/01/98 23:59:59.99101/01/98 23:59:59.991
1998-01-01 23:59:59.9901998-01-01 23:59:59.990

ANSI 和 ISO 8601 標準ANSI and ISO 8601 Compliance

datetime 不符合 ANSI 或 ISO 8601 標準。datetime isn't ANSI or ISO 8601 compliant.

轉換日期和時間資料Converting Date and Time Data

當您轉換成日期與時間資料類型時,SQL ServerSQL Server 會拒絕所有無法辨識為日期或時間的值。When you convert to date and time data types, SQL ServerSQL Server rejects all values it can't 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).

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

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

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

DECLARE @date date = '12-21-16';  
DECLARE @datetime datetime = @date;  
  
SELECT @datetime AS '@datetime', @date AS '@date';  
  
--Result  
--@datetime               @date  
------------------------- ----------  
--2016-12-21 00:00:00.000 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(n) 值的小數有效位數大於三位數,系統會截斷此值以配合其大小。When the fractional precision of the time(n) value is greater than three digits, the value will be truncated to fit. 下列範例顯示將 time(4) 值轉換成 datetime 值的結果。The following example shows the results of converting a time(4) value to a datetime value.

DECLARE @time time(4) = '12:10:05.1237';  
DECLARE @datetime datetime = @time;  
  
SELECT @datetime AS '@datetime', @time AS '@time';  
  
--Result  
--@datetime               @time  
------------------------- -------------  
--1900-01-01 12:10:05.123 12:10:05.1237  

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

DECLARE @smalldatetime smalldatetime = '12-01-16 12:32';  
DECLARE @datetime datetime = @smalldatetime;  
  
SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime';  
  
--Result  
--@datetime               @smalldatetime  
------------------------- -----------------------  
--2016-12-01 12:32:00.000 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(n) 值的小數有效位數大於三位數,系統會截斷此值。When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value will be truncated. 下列範例顯示將 datetimeoffset(4) 值轉換成 datetime 值的結果。The following example shows the results of converting a datetimeoffset(4) value to a datetime value.

DECLARE @datetimeoffset datetimeoffset(4) = '1968-10-23 12:45:37.1234 +10:0';  
DECLARE @datetime datetime = @datetimeoffset;  
  
SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';  
  
--Result  
--@datetime               @datetimeoffset  
------------------------- ------------------------------  
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237 +01:0   

datetime2(n) 轉換時,會複製日期和時間。When the conversion is from datetime2(n), the date and time are copied. 如果 datetime2(n) 值的小數有效位數大於三位數,系統會截斷此值。When the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated. 下列範例顯示將 datetime2(4) 值轉換成 datetime 值的結果。The following example shows the results of converting a datetime2(4) value to a datetime value.

DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237';  
DECLARE @datetime datetime = @datetime2;  
  
SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';  
  
--Result  
--@datetime               @datetime2  
------------------------- ------------------------  
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237  

範例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)