DATENAME (TSQL Function)

Returns the specified part of the date, as string value.

Syntax

DATENAME ( datepart , datetime_expression )

Arguments

datepart

part of the date to return.
See the list of valid values for datepart in the table below.

datetime_expression

is an expression of type date, time, datetime.

Return Types

Returns varchar.

Remarks

List of valid values for datepart:

datepart	Abbreviations	Result
year	yy , yyyy	[0001, 9999]
quarter	qq , q	[1, 4]
month	mm , m	January ... December. Depends on current LANGUAGE.
dayofyear	dy , y	[1, 366] leap year has 366 days
day	dd , d	[1, 31]
week	wk , ww	January 1st belongs to week 1. First day of week depends on the current LANGUAGE.
weekday	dw	Monday ... Sunday. Depends on current LANGUAGE.
hour	hh	[0, 23]
minute	mi , n	[0, 59]
second	ss , s	[0, 59]
millisecond	ms	[0, 999]
microsecond	mcs	[0, 999999]
nanosecond	ns	[0, 999999999]

Abbreviations can be used instead of the datepart full name. E.g. second, ss and s are equivalent.

For week, the result depends on the current setting for the first day of week. This setting can be modified with SET LANGUAGE or SET DATEFIRST. See also examples in DATEPART

Result for month and weekday depends on the current LANGUAGE.

Examples

DECLARE @dt datetime = '2007-10-30 12:15:32.123456789'

SELECT 'year' as datepart, DATENAME(year, @dt) as value
UNION ALL
SELECT 'quarter',          DATENAME(quarter, @dt)
UNION ALL
SELECT 'month',            DATENAME(month, @dt)
UNION ALL
SELECT 'dayofyear',        DATENAME(dayofyear, @dt)
UNION ALL
SELECT 'day',              DATENAME(day, @dt)
UNION ALL
SELECT 'week',             DATENAME(week, @dt)
UNION ALL
SELECT 'weekday',          DATENAME(weekday, @dt)
UNION ALL
SELECT 'hour',             DATENAME(hour, @dt)
UNION ALL
SELECT 'minute',           DATENAME(minute, @dt)
UNION ALL
SELECT 'second',           DATENAME(second, @dt)
UNION ALL
SELECT 'millisecond',      DATENAME(millisecond, @dt)
UNION ALL
SELECT 'microsecond',      DATENAME(microsecond, @dt)
UNION ALL
SELECT 'nanosecond',       DATENAME(nanosecond, @dt)

The result is:

datepart   |value                                                       |
-----------+------------------------------------------------------------+
year       |2007                                                        |
quarter    |4                                                           |
month      |October                                                     |
dayofyear  |303                                                         |
day        |30                                                          |
week       |44                                                          |
weekday    |Tuesday                                                     |
hour       |12                                                          |
minute     |15                                                          |
second     |32                                                          |
millisecond|123                                                         |
microsecond|123456                                                      |
nanosecond |123456789                                                   |