DATENAME (Transact-SQL)

Returns a character string representing the specified datepart of the specified date.

Important

This feature has changed from earlier versions of SQL Server. For more information, see Behavior Changes to Database Engine Features in SQL Server 2005.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DATENAME ( datepart ,date )

Arguments

  • datepart
    Is the parameter that specifies the part of the date to return. The following table lists dateparts and abbreviations recognized by Microsoft SQL Server 2005.

    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

    The weekday (dw) datepart returns the day of the week, such as Sunday, Monday, and so on.

  • date
    Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type for dates after January 1, 1753. Store as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0. For more information about how to specify dates, see Date and Time (Transact-SQL). For more information about how to specify time values, see Time Formats.

    If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two-digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that comes before the cutoff year. For example, if a two-digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 50 is interpreted as 1950. To avoid ambiguity, use four-digit years.

Return Types

nvarchar

Remarks

The SQL Server 2005 Database Engine automatically converts between character and datetime values as needed, for example, when you compare a character value with a datetime value.

Examples

The following example extracts the month name from the date returned by GETDATE.

SELECT DATENAME(month, GETDATE()) AS 'Month Name';

Here is the result set.

Month Name

------------------------------

February

The following example extracts the month name from a column.

USE AdventureWorks;
GO
SELECT StartDate, DATENAME(month,StartDate) AS StartMonth
FROM Production.WorkOrder
WHERE WorkOrderID = 1;
GO

Here is the result set.

StartDate StartMonth

-------------------------------- ------------

2001-07-04 00:00:00.000 July

The following examples show each datepart argument with the date argument in this format, 'YYYY-MM-DDTHH:MM:SS.xxx'.

SELECT DATENAME(year,'1995-10-30 12:15:32.123');
SELECT DATENAME(yy,'1995-10-30 12:15:32.123');
SELECT DATENAME(yyyy,'1995-10-30 12:15:32.123');
-- Return: 1995

SELECT DATENAME(quarter,'1995-10-30 12:15:32.123');
SELECT DATENAME(qq,'1995-10-30 12:15:32.123');
SELECT DATENAME(q,'1995-10-30 12:15:32.123');
-- Return: 4

SELECT DATENAME(month,'1995-10-30 12:15:32.123');
SELECT DATENAME(mm,'1995-10-30 12:15:32.123');
SELECT DATENAME(m,'1995-10-30 12:15:32.123');
-- Return: October

SELECT DATENAME(dayofyear,'1995-10-30 12:15:32.123');
SELECT DATENAME(dy,'1995-10-30 12:15:32.123');
SELECT DATENAME(y,'1995-10-30 12:15:32.123');
-- Return: 303

SELECT DATENAME(day,'1995-10-30 12:15:32.123');
SELECT DATENAME(dd,'1995-10-30 12:15:32.123');
SELECT DATENAME(d,'1995-10-30 12:15:32.123');
-- Return: 30

SELECT DATENAME(week,'1995-10-30 12:15:32.123');
SELECT DATENAME(wk,'1995-10-30 12:15:32.123');
SELECT DATENAME(ww,'1995-10-30 12:15:32.123');
-- Return: 44

SELECT DATENAME(weekday,'1995-10-30 12:15:32.123');
SELECT DATENAME(dw,'1995-10-30 12:15:32.123');
-- Return: Monday

SELECT DATENAME(hour,'1995-10-30 12:15:32.123');
SELECT DATENAME(hour,'1995-10-30 12:15:32.123');
SELECT DATENAME(hh,'10/30/1995 12:15:32.123 PM');
SELECT DATENAME(hh,'10/30/1995 12:15:32.123 PM');
-- Return: 12

SELECT DATENAME(minute,'1995-10-30 12:15:32.123');
SELECT DATENAME(mi,'1995-10-30 12:15:32.123');
SELECT DATENAME(n,'1995-10-30 12:15:32.123');
-- Return: 15
 
SELECT DATENAME(second,'1995-10-30 12:15:32.123');
SELECT DATENAME(ss,'1995-10-30 12:15:32.123');
SELECT DATENAME(s,'1995-10-30 12:15:32.123');
-- Return: 32

SELECT DATENAME(millisecond,'1995-10-30 12:15:32.123');
SELECT DATENAME(ms,'1995-10-30 12:15:32.123');
-- Return: 123

Change History

Release History

17 July 2006

New content:
  • Added the examples.

See Also

Reference

CAST and CONVERT (Transact-SQL)
Data Types (Transact-SQL)
Date and Time Functions (Transact-SQL)

Other Resources

ISO 8601 Format
Alphabetic Date Format
Numeric Date Format
ODBC Datetime Format
Time Formats
Unseparated String Format

Help and Information

Getting SQL Server 2005 Assistance