DATENAME (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This function returns a character string representing the specified datepart of the specified date.
See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQL date and time data types and functions.
Transact-SQL syntax conventions
Syntax
DATENAME ( datepart , date )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
datepart
The specific part of the date argument that DATENAME
will return. This table lists all valid datepart arguments.
Note
DATENAME
does not accept user-defined variable equivalents for the datepart arguments.
datepart | Abbreviations |
---|---|
year | yy, yyyy |
quarter | qq, q |
month | mm, m |
dayofyear | dy, y |
day | dd, d |
week | wk, ww |
weekday | dw, w |
hour | hh |
minute | mi, n |
second | ss, s |
millisecond | ms |
microsecond | mcs |
nanosecond | ns |
TZoffset | tz |
ISO_WEEK | ISOWK, ISOWW |
date
An expression that can resolve to one of the following data types:
- date
- datetime
- datetimeoffset
- datetime2
- smalldatetime
- time
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
nvarchar
Return Value
- Each datepart and its abbreviations return the same value.
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. The return value depends on SET DATEFORMAT if date is a string literal of some formats. SET DATEFORMAT does not change the return value when the date is a column expression of a date or time data type.
When the date parameter has a date data type argument, the return value depends on the setting specified by SET DATEFIRST.
TZoffset datepart Argument
If the datepart argument is TZoffset (tz) and the date argument has no time zone offset, DATEADD
returns 0.
smalldatetime date Argument
When date is smalldatetime, DATENAME
returns seconds as 00.
Default Returned for a datepart That Is Not in the date Argument
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 .
For example, the default year-month-day for any date data type is 1900-01-01. This 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');
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. In this example, variable @t has a time data type. 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);
Remarks
Use DATENAME
in the following clauses:
- GROUP BY
- HAVING
- ORDER BY
- SELECT <list>
- WHERE
In SQL Server, DATENAME implicitly casts string literals as a datetime2 type. In other words, DATENAME
does not support the format YDM when the date is passed as a string. 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. 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.
datepart | Return value |
---|---|
year, yyyy, yy | 2007 |
quarter, qq, q | 4 |
month, mm, m | October |
dayofyear, dy, y | 303 |
day, dd, d | 30 |
week, wk, ww | 44 |
weekday, dw | Tuesday |
hour, hh | 12 |
minute, n | 15 |
second, ss, s | 32 |
millisecond, ms | 123 |
microsecond, mcs | 123456 |
nanosecond, ns | 123456700 |
TZoffset, tz | +05:10 |
ISO_WEEK, ISOWK, ISOWW | 44 |
Azure Synapse Analytics and Analytics Platform System (PDW)
This example returns the date parts for the specified date. 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.
datepart | Return value |
---|---|
year, yyyy, yy | 2007 |
quarter, qq, q | 4 |
month, mm, m | October |
dayofyear, dy, y | 303 |
day, dd, d | 30 |
week, wk, ww | 44 |
weekday, dw | Tuesday |
hour, hh | 12 |
minute, n | 15 |
second, ss, s | 32 |
millisecond, ms | 123 |
microsecond, mcs | 123456 |
nanosecond, ns | 123456700 |
TZoffset, tz | +05:10 |
ISO_WEEK, ISOWK, ISOWW | 44 |
See also
Pripomienky
https://aka.ms/ContentUserFeedback.
Pripravujeme: V priebehu roka 2024 postupne zrušíme službu Problémy v službe GitHub ako mechanizmus pripomienok týkajúcich sa obsahu a nahradíme ju novým systémom pripomienok. Ďalšie informácie nájdete na stránke:Odoslať a zobraziť pripomienky pre