DATENAME (Transact-SQL)DATENAME (Transact-SQL)

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

此函式會傳回字元字串,代表指定之 date 的指定 datepartThis function returns a character string 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

DATENAME ( datepart , date )  

引數Arguments

datepartdatepart
DATENAME 會傳回之 date 引數的特定部分。The specific part of the date argument that DATENAME will return. 此表格會列出所有有效的 datepart 引數。This table lists all valid datepart arguments.

注意

DATENAME 不會接受 datepart 引數的使用者定義變數對等項目。DATENAME 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 dw, wdw, w
hourhour hhhh
minuteminute mi, nmi, n
secondsecond ss, sss, s
millisecondmillisecond msms
microsecondmicrosecond mcsmcs
nanosecondnanosecond nsns
TZoffsetTZoffset tztz
ISO_WEEKISO_WEEK ISOWK, ISOWWISOWK, ISOWW

datedate

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

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

針對 dateDATENAME 會接受資料行運算式、運算式、字串常值或使用者定義變數。For date, DATENAME 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

nvarcharnvarchar

傳回值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.

date 參數具有 date 資料類型引數時,傳回值就會根據 SET DATEFIRST 所指定的設定而不同。When the date parameter has a date data type argument, the return value depends on the setting specified by SET DATEFIRST.

TZoffset datepart 引數TZoffset datepart Argument

如果 datepart 引數是 TZoffset (tz) 而且 date 引數沒有時區位移時,DATEADD 就會傳回 0。If the datepart argument is TZoffset (tz) and the date argument has no time zone offset, DATEADD returns 0.

smalldatetime date 引數smalldatetime date Argument

datesmalldatetime 時,DATENAME 會以 00 形式傳回秒數。When date is smalldatetime, DATENAME returns seconds as 00.

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

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

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

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

如果 date 指定為變數或資料表資料行,而該變數或資料行的資料類型沒有指定的 datepartDATENAME 會傳回錯誤 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, DATENAME 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 DATENAME(year, @t);  

RemarksRemarks

在下列子句中使用 DATENAMEUse DATENAME in the following clauses:

  • GROUP BYGROUP BY
  • HAVINGHAVING
  • ORDER BYORDER BY
  • SELECT <list>SELECT <list>
  • WHEREWHERE

SQL Server 2017SQL Server 2017 中,DATENAME 會隱含地將字串常值轉換為 datetime2 類型。In SQL Server 2017SQL Server 2017, DATENAME implicitly casts string literals as a datetime2 type. 換句話說,將日期當作字串傳遞時,DATENAME 不支援 YDM 格式。In other words, 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 date parts for the specified date. 請將資料表中的 datepart 值取代為 SELECT 陳述式中的 datepart 引數:Substitute a datepart value from the table for the datepart argument in the SELECT statement:

SELECT DATENAME(datepart,'2007-10-30 12:15:32.1234567 +05:10');

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

datepartdatepart 傳回值Return value
year、yyyy、yyyear, yyyy, yy 20072007
quarter、qq、qquarter, qq, q 44
month、mm、mmonth, mm, m 十月October
dayofyear、dy、ydayofyear, dy, y 303303
day、dd、dday, dd, d 3030
week、wk、wwweek, wk, ww 4444
weekday、dwweekday, dw 星期二Tuesday
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
ISO_WEEK、ISOWK、ISOWWISO_WEEK, ISOWK, ISOWW 4444

Azure SQL 資料倉儲Azure SQL Data Warehouse 平行處理資料倉儲Parallel Data Warehouseand 平行處理資料倉儲Parallel Data Warehouse

此範例會針對指定的日期傳回日期部分。This example returns the date parts for the specified date. 請將資料表中的 datepart 值取代為 SELECT 陳述式中的 datepart 引數:Substitute a datepart value from the table for the datepart argument in the SELECT statement:

SELECT DATENAME(datepart,'2007-10-30 12:15:32.1234567 +05:10');  

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

datepartdatepart 傳回值Return value
year、yyyy、yyyear, yyyy, yy 20072007
quarter、qq、qquarter, qq, q 44
month、mm、mmonth, mm, m 十月October
dayofyear、dy、ydayofyear, dy, y 303303
day、dd、dday, dd, d 3030
week、wk、wwweek, wk, ww 4444
weekday、dwweekday, dw 星期二Tuesday
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
ISO_WEEK、ISOWK、ISOWWISO_WEEK, ISOWK, ISOWW 4444

另請參閱See also

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