日期和時間資料類型與函數 (Transact-SQL)Date and Time Data Types and Functions (Transact-SQL)

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

本主題中的各節涵蓋所有 Transact-SQLTransact-SQL 日期和時間資料類型與函式。The sections in this topic cover all Transact-SQLTransact-SQL date and time data types and functions.

日期和時間資料類型Date and Time data types

Transact-SQLTransact-SQL 日期和時間資料類型會列在下表中:The Transact-SQLTransact-SQL date and time data types are listed in the following table:

資料類型Data type [格式]Format 範圍Range 精確度Accuracy 儲存體大小 (位元組)Storage size (bytes) 使用者自訂的小數秒數有效位數User-defined fractional second precision 時區位移Time zone offset
timetime hh:mm:ss[.nnnnnnn]hh:mm:ss[.nnnnnnn] 00:00:00.0000000 到 23:59:59.999999900:00:00.0000000 through 23:59:59.9999999 100 奈秒100 nanoseconds 3 到 53 to 5 Yes No
datedate YYYY-MM-DDYYYY-MM-DD 0001-01-01 到 31.12.990001-01-01 through 9999-12-31 1 日1 day 33 No No
smalldatetimesmalldatetime YYYY-MM-DD hh:mm:ssYYYY-MM-DD hh:mm:ss 1900-01-01 到 2079-06-061900-01-01 through 2079-06-06 1 分鐘1 minute 44 No No
datetimedatetime YYYY-MM-DD hh:mm:ss[.nnn]YYYY-MM-DD hh:mm:ss[.nnn] 1753-01-01 到 9999-12-311753-01-01 through 9999-12-31 0.00333 秒鐘0.00333 second 88 No No
datetime2datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn]YYYY-MM-DD hh:mm:ss[.nnnnnnn] 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.99999990001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 100 奈秒100 nanoseconds 6 到 86 to 8 Yes No
datetimeoffsetdatetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mmYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999 (以 UTC 為單位)0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) 100 奈秒100 nanoseconds 8 到 108 to 10 Yes Yes

注意

Transact-SQLTransact-SQL rowversion 資料類型不是日期或時間資料類型。The Transact-SQLTransact-SQL rowversion data type is not a date or time data type. timestamprowversion 的已取代同義字。timestamp is a deprecated synonym for rowversion.

日期和時間函式Date and Time functions

下表列出 Transact-SQLTransact-SQL 日期和時間函式。The following tables list the Transact-SQLTransact-SQL date and time functions. 如需決定性的詳細資訊,請參閱決定性與非決定性函式See Deterministic and Nondeterministic Functions for more information about determinism.

傳回系統日期和時間值的函式Function That Return System Date and Time Values

Transact-SQLTransact-SQL 會從執行 SQL ServerSQL Server 執行個體之電腦的作業系統衍生所有系統日期和時間值。derives all system date and time values from the operating system of the computer on which the instance of SQL ServerSQL Server runs.

較高精確度的系統日期和時間函數Higher-Precision System Date and Time Functions

SQL Server 2017SQL Server 2017 會透過使用 GetSystemTimeAsFileTime() Windows API 來衍生日期和時間值。derives the date and time values through use of the GetSystemTimeAsFileTime() Windows API. 精確度取決於執行 SQL ServerSQL Server 執行個體的電腦硬體和 Windows 版本。The accuracy depends on the computer hardware and version of Windows on which the instance of SQL ServerSQL Server running. 此 API 的精確度是固定於 100 奈秒。This API has a precision fixed at 100 nanoseconds. 請使用 GetSystemTimeAdjustment() Windows API 來判斷正確性。Use the GetSystemTimeAdjustment() Windows API to determine the accuracy.

函數Function 語法Syntax 傳回值Return value 傳回資料類型Return data type 決定性Determinism
SYSDATETIMESYSDATETIME SYSDATETIME ()SYSDATETIME () 傳回 datetime2(7) 值,此值包含執行 SQL ServerSQL Server 執行個體之電腦的日期和時間。Returns a datetime2(7) value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 傳回的值不包含時區時差。The returned value does not include the time zone offset. datetime2(7)datetime2(7) 不具決定性Nondeterministic
SYSDATETIMEOFFSETSYSDATETIMEOFFSET SYSDATETIMEOFFSET ( )SYSDATETIMEOFFSET ( ) 傳回 datetimeoffset(7) 值,此值包含執行 SQL ServerSQL Server 執行個體之電腦的日期和時間。Returns a datetimeoffset(7) value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 傳回的值包含時區時差。The returned value includes the time zone offset. datetimeoffset(7)datetimeoffset(7) 不具決定性Nondeterministic
SYSUTCDATETIMESYSUTCDATETIME SYSUTCDATETIME ( )SYSUTCDATETIME ( ) 傳回 datetime2(7) 值,此值包含執行 SQL ServerSQL Server 執行個體之電腦的日期和時間。Returns a datetime2(7) value containing the date and time of the computer on which the instance of SQL ServerSQL Server is running. 此函式是以國際標準時間 (Coordinated Universal Time,UTC) 傳回日期和時間值。The function returns the date and time values as UTC time (Coordinated Universal Time). datetime2(7)datetime2(7) 不具決定性Nondeterministic

較低精確度的系統日期和時間函數Lower-Precision System Date and Time Functions

函數Function 語法Syntax 傳回值Return value 傳回資料類型Return data type 決定性Determinism
CURRENT_TIMESTAMPCURRENT_TIMESTAMP CURRENT_TIMESTAMPCURRENT_TIMESTAMP 傳回 datetime 值,此值包含執行 SQL ServerSQL Server 執行個體之電腦的日期和時間。Returns a datetime value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 傳回的值不包含時區時差。The returned value does not include the time zone offset. datetimedatetime 不具決定性Nondeterministic
GETDATEGETDATE GETDATE ( )GETDATE ( ) 傳回 datetime 值,此值包含執行 SQL ServerSQL Server 執行個體之電腦的日期和時間。Returns a datetime value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 傳回的值不包含時區時差。The returned value does not include the time zone offset. datetimedatetime 不具決定性Nondeterministic
GETUTCDATEGETUTCDATE GETUTCDATE ( )GETUTCDATE ( ) 傳回 datetime 值,此值包含執行 SQL ServerSQL Server 執行個體之電腦的日期和時間。Returns a datetime value containing the date and time of the computer on which the instance of SQL ServerSQL Server runs. 此函式是以國際標準時間 (Coordinated Universal Time,UTC) 傳回日期和時間值。The function returns the date and time values as UTC time (Coordinated Universal Time). datetimedatetime 不具決定性Nondeterministic

傳回日期和時間部分的函式Functions That Return Date and Time Parts

函數Function 語法Syntax 傳回值Return value 傳回資料類型Return data type 決定性Determinism
DATENAMEDATENAME DATENAME ( datepart , date )DATENAME ( datepart , date ) 傳回字元字串,代表指定日期的指定 datepartReturns a character string representing the specified datepart of the specified date. nvarcharnvarchar 不具決定性Nondeterministic
DATEPARTDATEPART DATEPART ( datepart , date )DATEPART ( datepart , date ) 傳回一個整數,代表指定 date 的指定 datepartReturns an integer representing the specified datepart of the specified date. intint 不具決定性Nondeterministic
DAYDAY DAY ( date )DAY ( date ) 傳回一個整數,代表指定 date 的日 (Day) 部分。Returns an integer representing the day part of the specified date. intint 具決定性Deterministic
MONTHMONTH MONTH ( date )MONTH ( date ) 傳回一個整數,代表指定 date 的月 (Month) 部分。Returns an integer representing the month part of a specified date. intint 具決定性Deterministic
YEARYEAR YEAR ( date )YEAR ( date ) 傳回一個整數,代表指定 date 的年 (Year) 部分。Returns an integer representing the year part of a specified date. intint 具決定性Deterministic

從各自部分取得日期和時間值的函式Functions That Return Date and Time Values from Their Parts

函數Function 語法Syntax 傳回值Return value 傳回資料類型Return data type 決定性Determinism
DATEFROMPARTSDATEFROMPARTS DATEFROMPARTS ( year, month, day )DATEFROMPARTS ( year, month, day ) 傳回指定之年、月、日的 date 值。Returns a date value for the specified year, month, and day. datedate 具決定性Deterministic
DATETIME2FROMPARTSDATETIME2FROMPARTS DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision)DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision) 以指定的精確度傳回指定日期與時間的 datetime2 值。Returns a datetime2 value for the specified date and time, with the specified precision. datetime2( precision )datetime2( precision ) 具決定性Deterministic
DATETIMEFROMPARTSDATETIMEFROMPARTS DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds)DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds) 傳回指定日期和時間的 datetime 值。Returns a datetime value for the specified date and time. datetimedatetime 具決定性Deterministic
DATETIMEOFFSETFROMPARTSDATETIMEOFFSETFROMPARTS DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision) 以指定的時差和精確度傳回指定日期和時間的 datetimeoffset 值。Returns a datetimeoffset value for the specified date and time, with the specified offsets and precision. datetimeoffset( precision )datetimeoffset( precision ) 具決定性Deterministic
SMALLDATETIMEFROMPARTSSMALLDATETIMEFROMPARTS SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) 傳回指定日期和時間的 smlldatetime 值。Returns a smalldatetime value for the specified date and time. smalldatetimesmalldatetime 具決定性Deterministic
TIMEFROMPARTSTIMEFROMPARTS TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) 以指定的精確度傳回指定時間的 time 值。Returns a time value for the specified time, with the specified precision. time( precision )time( precision ) 具決定性Deterministic

傳回日期和時間差異值的函式Functions That Return Date and Time Difference Values

函數Function 語法Syntax 傳回值Return value 傳回資料類型Return data type 決定性Determinism
DATEDIFFDATEDIFF DATEDIFF ( datepart , startdate , enddate )DATEDIFF ( datepart , startdate , enddate ) 傳回跨越兩個指定日期的日期或時間 datepart 界限數字。Returns the number of date or time datepart boundaries, crossed between two specified dates. intint 具決定性Deterministic
DATEDIFF_BIGDATEDIFF_BIG DATEDIFF_BIG ( datepart , startdate , enddate )DATEDIFF_BIG ( datepart , startdate , enddate ) 傳回跨越兩個指定日期的日期或時間 datepart 界限數字。Returns the number of date or time datepart boundaries, crossed between two specified dates. bigintbigint 具決定性Deterministic

修改日期和時間值的函式Functions That Modify Date and Time Values

函數Function 語法Syntax 傳回值Return value 傳回資料類型Return data type 決定性Determinism
DATEADDDATEADD DATEADD (datepart , number , date )DATEADD (datepart , number , date ) 透過在指定 date 的指定 datepart 中新增間隔,傳回新的 datetime 值。Returns a new datetime value by adding an interval to the specified datepart of the specified date. date 引數的資料類型The data type of the date argument 具決定性Deterministic
EOMONTHEOMONTH EOMONTH ( start_date [, month_to_add ] )EOMONTH ( start_date [, month_to_add ] ) 以選擇性位移,傳回包含指定日期的當月最後一天。Returns the last day of the month containing the specified date, with an optional offset. 傳回類型是 start_date 引數的類型,或者是 date 資料類型。Return type is the type of the start_date argument, or alternately, the date data type. 具決定性Deterministic
SWITCHOFFSETSWITCHOFFSET SWITCHOFFSET (DATETIMEOFFSET , time_zone)SWITCHOFFSET (DATETIMEOFFSET , time_zone) SWITCHOFFSET 會變更 DATETIMEOFFSET 值的時區時差,並保留 UTC 值。SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value, and preserves the UTC value. 具有 DATETIMEOFFSET 之毫秒精確度的 datetimeoffsetdatetimeoffset with the fractional precision of the DATETIMEOFFSET 具決定性Deterministic
TODATETIMEOFFSETTODATETIMEOFFSET TODATETIMEOFFSET (expression , time_zone)TODATETIMEOFFSET (expression , time_zone) TODATETIMEOFFSET 會將 datetime2 值轉換成 datetimeoffset 值。TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. TODATETIMEOFFSET 會針對指定的 time_zone 以當地時間解譯 datetime2 值。TODATETIMEOFFSET interprets the datetime2 value in local time, for the specified time_zone. 具有 datetime 引數之毫秒精確度的 datetimeoffsetdatetimeoffset with the fractional precision of the datetime argument 具決定性Deterministic

設定或傳回工作階段格式函式的函式Functions That Set or Return Session Format Functions

函數Function 語法Syntax 傳回值Return value 傳回資料類型Return data type 決定性Determinism
@@DATEFIRST@@DATEFIRST @@DATEFIRST@@DATEFIRST 傳回 SET DATEFIRST 之工作階段的目前值。Returns the current value, for the session, of SET DATEFIRST. tinyinttinyint 不具決定性Nondeterministic
SET DATEFIRSTSET DATEFIRST SET DATEFIRST { number | * @***number_var }SET DATEFIRST { number | *@***number_var } 將一週的第一天設為 1-7 其中一個數字。Sets the first day of the week to a number from 1 through 7. 不適用Not applicable 不適用Not applicable
SET DATEFORMATSET DATEFORMAT SET DATEFORMAT { format | @ format_var }SET DATEFORMAT { format | @format_var } 設定輸入 datetimesmalldatetime 資料時,日期部分 (月/日/年) 的順序。Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data. 不適用Not applicable 不適用Not applicable
@@LANGUAGE@@LANGUAGE @@LANGUAGE@@LANGUAGE 傳回目前使用中的語言名稱。Returns the name of the language in current used. @@LANGUAGE 不是日期或時間函式。@@LANGUAGE is not a date or time function. 不過,語言設定可能會影響日期函數的輸出。However, the language setting can affect the output of date functions. 不適用Not applicable 不適用Not applicable
SET LANGUAGESET LANGUAGE SET LANGUAGE { [ N ] ' language ' | * @***language_var }SET LANGUAGE { [ N ] 'language' | *@***language_var } 設定工作階段和系統訊息的語言環境。Sets the language environment for the session and system messages. SET LANGUAGE 不是日期或時間函數。SET LANGUAGE is not a date or time function. 不過,語言設定會影響日期函數的輸出。However, the language setting affects the output of date functions. 不適用Not applicable 不適用Not applicable
sp_helplanguagesp_helplanguage sp_helplanguage [ [ @language = ] ' language ' ]sp_helplanguage [ [ @language = ] 'language' ] 傳回所有支援語言之日期格式的詳細資訊。Returns information about date formats of all supported languages. sp_helplanguage 不是日期或時間預存程序。sp_helplanguage is not a date or time stored procedure. 不過,語言設定會影響日期函數的輸出。However, the language setting affects the output of date functions. 不適用Not applicable 不適用Not applicable

驗證日期和時間值的函式Functions That Validate Date and Time Values

函數Function 語法Syntax 傳回值Return value 傳回資料類型Return data type 決定性Determinism
ISDATEISDATE ISDATE ( expression )ISDATE ( expression ) 判斷 datetimesmalldatetime 輸入運算式是否具有有效的日期或時間值。Determines whether a datetime or smalldatetime input expression has a valid date or time value. intint 只有在搭配 CONVERT 函數使用、已指定 CONVERT 樣式參數,而且樣式不等於 0、100、9 或 109 時,ISDATE 才具有決定性。ISDATE is deterministic only used with the CONVERT function, when the CONVERT style parameter is specified, and when style is not equal to 0, 100, 9, or 109.

與日期和時間相關的主題Date and time-related topics

主題Topic DescriptionDescription
CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL) 提供將日期和時間值在字串常值與其他日期和時間格式之間來回轉換的相關資訊。Provides information about the conversion of date and time values to and from string literals, and other date and time formats.
撰寫國際通用的 Transact-SQL 陳述式Write International Transact-SQL Statements 提供一些指導方針,讓使用 Transact-SQLTransact-SQL 陳述式的資料庫與資料庫應用程式能從某種語言移植至另一種語言,或可支援多種語言。Provides guidelines for portability of databases and database applications that use Transact-SQLTransact-SQL statements from one language to another, or that support multiple languages.
ODBC 純量函式 (Transact-SQL)ODBC Scalar Functions (Transact-SQL) 提供可用於 Transact-SQLTransact-SQL 陳述式之 ODBC 純量函式的相關資訊。Provides information about ODBC scalar functions available for use in Transact-SQLTransact-SQL statements. 這包括 ODBC 日期和時間函式。This includes ODBC date and time functions.
AT TIME ZONE (Transact-SQL)AT TIME ZONE (Transact-SQL) 提供時區轉換。Provides time zone conversion.

另請參閱See also

函數Functions
資料類型 (Transact-SQL)Data Types (Transact-SQL)