datepart

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

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset	tz

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 two digit year cutoff 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 default language 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.

For versions of SQL Server later than SQL Server 2000, when the date parameter has a date data type argument, the return value depends on the setting specified by using SET DATEFIRST.

TZoffset datepart Argument

smalldatetime date Argument

Default Returned for a datepart That Is Not in the date Argument

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');

SELECT DATENAME(hour, '2007-06-01')
    ,DATENAME(minute, '2007-06-01')
    ,DATENAME(second, '2007-06-01');

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

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.

Reference

Help and Information