DATEPART (Transact-SQL)DATEPART (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

此函式會傳回整數,代表指定 date 的指定 datepartThis function returns an integer representing the specified datepart of the specified date.

如需所有 Transact-SQLTransact-SQL 日期和時間資料類型與函式的概觀,請參閱日期和時間資料類型與函式 (Transact-SQL)See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQLTransact-SQL date and time data types and functions.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

DATEPART ( datepart , date )  

引數Arguments

datepartdatepart
date 引數的特定部分,其 DATEPART 會傳回整數The specific part of the date argument for which DATEPART will return an integer. 此表格會列出所有有效的 datepart 引數。This table lists all valid datepart arguments.

注意

DATEPART 不會接受 datepart 引數的使用者定義變數對等項目。DATEPART does not accept user-defined variable equivalents for the datepart arguments.

datepartdatepart 縮寫Abbreviations
yearyear yy, yyyyyy, yyyy
quarterquarter qq, qqq, q
monthmonth mm, mmm, m
dayofyeardayofyear dy, ydy, y
dayday dd, ddd, d
weekweek wk, wwwk, ww
weekdayweekday dwdw
hourhour hhhh
minuteminute mi, nmi, n
secondsecond ss, sss, s
millisecondmillisecond msms
microsecondmicrosecond mcsmcs
nanosecondnanosecond nsns
TZoffsetTZoffset tztz
ISO_WEEKISO_WEEK isowkisowwisowk, isoww

datedate
可解析成下列其中一個資料類型的運算式:An expression that resolves to one of the following data types:

  • datedate
  • datetimedatetime
  • datetimeoffsetdatetimeoffset
  • datetime2datetime2
  • smalldatetimesmalldatetime
  • timetime

針對 dateDATEPART 會接受資料行運算式、運算式、字串常值或使用者定義變數。For date, DATEPART will accept a column expression, expression, string literal, or user-defined variable. 請使用四位數年份以避免模糊不清的問題。Use four-digit years to avoid ambiguity issues. 如需兩位數年份的資訊,請參閱設定兩位數年份的截止伺服器設定選項See Configure the two digit year cutoff Server Configuration Option for information about two-digit years.

傳回類型Return Type

intint

傳回值Return Value

每個 datepart 及其縮寫都會傳回相同的值。Each datepart and its abbreviations return the same value.

傳回值會取決於使用 SET LANGUAGE 及登入的設定 default language 伺服器設定選項而設定的語言環境,而有所不同。The return value depends on the language environment set by using SET LANGUAGE, and by the Configure the default language Server Configuration Option of the login. 如果 date 是某些格式的字串常值,傳回值就會取決於 SET DATEFORMATThe return value depends on SET DATEFORMAT if date is a string literal of some formats. 當 date 是日期或時間資料類型的資料行運算式時,SET DATEFORMAT 並不會變更傳回值。SET DATEFORMAT does not change the return value when the date is a column expression of a date or time data type.

此表格針對陳述式 SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10') 列出所有 datepart 引數和對應的傳回值。This table lists all datepart arguments, with corresponding return values, for the statement SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10'). date 引數具有 datetimeoffset(7) 資料類型。The date argument has a datetimeoffset(7) data type. nanosecond datepart 傳回值的最後兩個位數一定是 00,而且此值具有 9 位數的小數位數:The last two positions of the nanosecond datepart return value are always 00 and this value has a scale of 9:

.123456700.123456700

datepartdatepart 傳回值Return value
year、yyyy、yyyear, yyyy, yy 20072007
quarter、qq、qquarter, qq, q 44
month、mm、mmonth, mm, m 1010
dayofyear、dy、ydayofyear, dy, y 303303
day、dd、dday, dd, d 3030
week、wk、wwweek, wk, ww 4545
weekday、dwweekday, dw 11
hour、hhhour, hh 1212
minute、nminute, n 1515
second、ss、ssecond, ss, s 3232
millisecond、msmillisecond, ms 123123
microsecond、mcsmicrosecond, mcs 123456123456
nanosecond、nsnanosecond, ns 123456700123456700
TZoffset、tzTZoffset, tz 310310

Week 和 weekday datepart 引數Week and weekday datepart arguments

針對 week (wkww) 或 weekday (dw) datepartDATEPART 傳回值會取決於 SET DATEFIRST 所設定的值。For a week (wk, ww) or weekday (dw) datepart, the DATEPART return value depends on the value set by SET DATEFIRST.

任何一年的 1 月 1 日皆定義 weekdatepart 的起始數字。January 1 of any year defines the starting number for the weekdatepart. 例如:For example:

DATEPART (wk, 'Jan 1, xxxx') = 1DATEPART (wk, 'Jan 1, xxxx') = 1

其中 xxxx 是任何一年。where xxxx is any year.

此表格顯示下列日期之 weekweekday datepart 的傳回值:This table shows the return value for the week and weekday datepart for

'2007-04-21 ''2007-04-21 '

(針對每個 SET DATEFIRST 引數)。for each SET DATEFIRST argument. 2007 年 1 月 1 日是星期一。January 1, 2007 falls on a Monday. 2007 年 4 月 21 日是星期六。April 21, 2007 falls on a Saturday. 針對美國英文,For U.S. English,

SET DATEFIRST 7 -- ( Sunday )SET DATEFIRST 7 -- ( Sunday )

可作為預設值。serves as the default. 設定 DATEFIRST 之後,請使用針對 datepart 資料表值建議的這個 SQL 陳述式:After setting DATEFIRST, use this suggested SQL statement for the datepart table values:

SELECT DATEPART(week, '2007-04-21 '), DATEPART(weekday, '2007-04-21 ')

SET DATEFIRSTSET DATEFIRST

引數 (argument)argument
weekweek

傳回returned
weekdayweekday

傳回returned
11 1616 66
22 1717 55
33 1717 44
44 1717 33
55 1717 22
66 1717 11
77 1616 77

year、month 和 day datepart 引數year, month, and day datepart Arguments

針對 DATEPART (yeardate)、DATEPART (monthdate) 和 DATEPART (daydate) 所傳回的值分別與 YEARMONTHDAY 函式傳回的值相同。The values that are returned for DATEPART (year, date), DATEPART (month, date), and DATEPART (day, date) are the same as those returned by the functions YEAR, MONTH, and DAY, respectively.

ISO_WEEK datepartISO_WEEK datepart

ISO 8601 包含 ISO 週-日期系統 (週數的編號系統)。ISO 8601 includes the ISO week-date system, a numbering system for weeks. 每一週都與星期四所在的年份相關聯。Each week is associated with the year in which Thursday occurs. 例如,2004 年第 1 週 (2004W01) 從 2003 年 12 月 29 日星期一到 2004 年 1 月 4 日星期日結束。For example, week 1 of 2004 (2004W01) covered Monday, 29 December 2003 to Sunday, 4 January 2004. 歐洲國家/地區通常會使用這種編號樣式。European countries / regions typically use this style of numbering. 非歐洲國家/地區通常不會使用。Non-European countries / regions typically do not use it.

注意:一年的最高週數可能是 52 或 53。Note: the highest week number in a year could be either 52 or 53.

不同國家/地區的編號系統可能不符合 ISO 標準。The numbering systems of different countries/regions might not comply with the ISO standard. 此表格顯示六種可能性:This table shows six possibilities:

每週的第一天First day of week 一年第一週包含First week of year contains 週指派兩次Weeks assigned two times 使用於Used by/in
星期日Sunday 1 月 1 日,1 January,

第一個星期六,First Saturday,

一年的第 1-7 天1-7 days of year
Yes United StatesUnited States
星期一Monday 1 月 1 日,1 January,

第一個星期日,First Sunday,

一年的第 1-7 天1-7 days of year
Yes 大部分歐洲國家 (地區) 和英國Most of Europe and the United Kingdom
星期一Monday 1 月 4 日,4 January,

第一個星期四,First Thursday,

一年的第 4-7 天4-7 days of year
No ISO 8601、挪威和瑞典ISO 8601, Norway, and Sweden
星期一Monday 1 月 7 日,7 January,

第一個星期一,First Monday,

一年的第 7 天7 days of year
No
星期三Wednesday 1 月 1 日,1 January,

第一個星期二,First Tuesday,

一年的第 1-7 天1-7 days of year
Yes
星期六Saturday 1 月 1 日,1 January,

第一個星期五,First Friday,

一年的第 1-7 天1-7 days of year
Yes

TZoffsetTZoffset

DATEPART 會以分鐘數 (帶正負號) 傳回 TZoffset (tz) 值。DATEPART returns the TZoffset (tz) value as the number of minutes (signed). 此陳述式會傳回 310 分鐘的時區位移:This statement returns a time zone offset of 310 minutes:

SELECT DATEPART (TZoffset, '2007-05-10  00:00:01.1234567 +05:10');  

DATEPART 轉譯 TZoffset 值的方式如下:DATEPART renders the TZoffset value as follows:

  • 若是 datetimeoffset 和 datetime2,TZoffset 傳回的時間位移會以分鐘為單位,而 datetime2 的位移一律是 0 分鐘。For datetimeoffset and datetime2, TZoffset returns the time offset in minutes, where the offset for datetime2 is always 0 minutes.
  • 針對可以隱含轉換成 datetimeoffsetdatetime2 的資料類型,DATEPART 會以分鐘數傳回時間位移。For data types that can implicitly convert to datetimeoffset or datetime2, DATEPART returns the time offset in minutes. 例外:其他日期/時間資料類型。Exception: other date / time data types.
  • 所有其他類型的參數都會導致錯誤。Parameters of all other types result in an error.

smalldatetime date 引數smalldatetime date Argument

針對 smalldatetime date 值,DATEPART 會以 00 形式傳回秒數。For a smalldatetime date value, DATEPART returns seconds as 00.

針對不在 date 引數中的 datepart 所傳回的預設值Default Returned for a datepart That Is Not in a date Argument

如果 date 引數的資料類型沒有指定的 datepart,只有在為 date 指定常值時,DATEPART 才會傳回該 datepart 的預設值。If the date argument data type does not have the specified datepart, DATEPART will return the default for that datepart only when a literal is specified for date.

例如,任何 date 資料類型的預設年-月-日都是 1900-01-01。For example, the default year-month-day for any date data type is 1900-01-01. 此陳述式具有 datepart 的日期部分引數、date 的時間引數,而且會傳回 1900, 1, 1, 1, 2This statement has date part arguments for datepart, a time argument for date, and it returns 1900, 1, 1, 1, 2.

SELECT DATEPART(year, '12:10:30.123')  
    ,DATEPART(month, '12:10:30.123')  
    ,DATEPART(day, '12:10:30.123')  
    ,DATEPART(dayofyear, '12:10:30.123')  
    ,DATEPART(weekday, '12:10:30.123');  

如果 date 指定為變數或資料表資料行,而該變數或資料行的資料類型沒有指定的 datepartDATEPART 會傳回錯誤 9810。If date is specified as a variable or table column, and the data type for that variable or column does not have the specified datepart, DATEPART will return error 9810. 在此範例中,變數 *@t* 具有 time 資料類型。In this example, variable *@t* has a time data type. 此範例會失敗,因為 DATEPART year 對 time 資料類型無效:The example fails because the date part year is invalid for the time data type:

DECLARE @t time = '12:10:30.123';   
SELECT DATEPART(year, @t);  

小數秒數Fractional seconds

這些陳述式顯示 DATEPART 會傳回小數秒數:These statements show that DATEPART returns fractional seconds:

SELECT DATEPART(millisecond, '00:00:01.1234567'); -- Returns 123  
SELECT DATEPART(microsecond, '00:00:01.1234567'); -- Returns 123456  
SELECT DATEPART(nanosecond,  '00:00:01.1234567'); -- Returns 123456700  

RemarksRemarks

DATEPART 可用於 SELECT 清單、WHERE、HAVING、GROUP BY 和 ORDER BY 子句中。DATEPART can be used in the select list, WHERE, HAVING, GROUP BY, and ORDER BY clauses.

DATEPART 會隱含地將字串常值轉換為 SQL Server 2017SQL Server 2017 中的 datetime2 類型。DATEPART implicitly casts string literals as a datetime2 type in SQL Server 2017SQL Server 2017. 這表示,將日期當做字串傳遞時,DATENAME 不支援 YDM 格式。This means that DATENAME does not support the format YDM when the date is passed as a string. 您必須明確地將字串轉換為 datetimesmalldatetime 類型,才能使用 YDM 格式。You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

範例Examples

此範例會傳回基底年份。This example returns the base year. 基底年份可協助日期計算。The base year helps with date calculations. 在此範例中,日期是由數字指定。In the example, a number specifies the date. 請注意,SQL ServerSQL Server 會將 0 解譯為 1900 年 1 月 1 日。Notice that SQL ServerSQL Server interprets 0 as January 1, 1900.

SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0);  
-- Returns: 1900    1    1 */  

此範例會傳回日期 12/20/1974 的日期部分。This example returns the day part of the date 12/20/1974.

-- Uses AdventureWorks  
  
SELECT TOP(1) DATEPART (day,'12/20/1974') FROM dbo.DimCustomer;  

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

--------
20

此範例會傳回日期 12/20/1974 的年份部分。This example returns the year part of the date 12/20/1974.

-- Uses AdventureWorks  
  
SELECT TOP(1) DATEPART (year,'12/20/1974') FROM dbo.DimCustomer;  

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

--------
1974

另請參閱See also

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