DATENAME (Transact-SQL)

DATENAME (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns a character string that represents the specified datepart of the specified date

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

  
DATENAME ( datepart , date )  

-- Azure SQL Data Warehouse and Parallel Data Warehouse  
DATENAME (datepart ,date )  

datepart
Is the part of the date to return. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

datepartAbbreviations
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
TZoffsettz
ISO_WEEKISOWK, ISOWW

date
Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal.

To avoid ambiguity, use four-digit years. For information about two-digit years, see Configure the two digit year cutoff Server Configuration Option.

nvarchar

  • 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 is dependant on SET DATEFORMAT if date is a string literal of some formats. SET DATEFORMAT does not affect 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 using SET DATEFIRST.

If datepart argument is TZoffset (tz) and the date argument has no time zone offset, 0 is returned.

When date is smalldatetime, seconds are returned as 00.

If the data type of the date argument does not have the specified datepart, the default for that datepart will be returned only when a literal is specified for date.

For example, the default year-month-day for any date data type is 1900-01-01. The following statement has date part arguments for datepart, a time argument for date, and 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, error 9810 is returned. The following code example fails because the date part year is not a valid for the time data type that is declared for the variable @t.

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

DATENAME can be used in the select list, WHERE, HAVING, GROUP BY, and ORDER BY clauses.

In SQL Server 2016, DATENAME implicitly casts string literals as a datetime2 type. This means that 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.

The following example returns the date parts for the specified date.

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

Here is the result set.

datepartReturn value
year, yyyy, yy2007
quarter, qq, q4
month, mm, mOctober
dayofyear, dy, y303
day, dd, d30
week, wk, ww44
weekday, dwTuesday
hour, hh12
minute, n15
second, ss, s32
millisecond, ms123
microsecond, mcs123456
nanosecond, ns123456700
TZoffset, tz310
ISO_WEEK, ISOWK, ISOWW44

The following example returns the date parts for the specified date.

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

Here is the result set.

datepartReturn value
year, yyyy, yy2007
quarter, qq, q4
month, mm, mOctober
dayofyear, dy, y303
day, dd, d30
week, wk, ww44
weekday, dwTuesday
hour, hh12
minute, n15
second, ss, s32
millisecond, ms123
microsecond, mcs123456
nanosecond, ns123456700
TZoffset, tz310
ISO_WEEK, ISOWK, ISOWW44

CAST and CONVERT (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft