Chapter 2: Transact-SQL Functions

datename

Description

Returns the specified datepart (the first argument) of the specified date or time (the second argument) as a character string. Takes a date, time, datetime, or smalldatetime value as its second argument.

Syntax

datename (datepart, date expression)

Parameters

datepart: is a date part or abbreviation. For a list of the date parts and abbreviations recognized by Adaptive Server, see “Date parts”.

date expression: is an expression of type datetime, smalldatetime, date, time, or a character string in a datetime format.

Examples

Example 1

Assumes a current date of November 20, 2000:

select datename(month, getdate())

November

Example 2

Finds the month name of a date:

declare @a date
select @a = "apr 12, 0001"
select datename(mm, @a)
------------------------------
    April

Example 3

Finds the seconds of a time:

declare @a time
select @a = "20:43:22"
select datename(ss, @a)
------------------------------
    22

Usage

datename, a date function, returns the name of the specified part (such as the month “June”) of a datetime or smalldatetime value, as a character string. If the result is numeric, such as “23” for the day, it is still returned as a character string.
For more information about date functions, see “Date functions”.
The date part weekday or dw returns the day of the week (Sunday, Monday, and so on) when used with datename.
Since smalldatetime is accurate only to the minute, when a smalldatetime value is used with datename, seconds and milliseconds are always 0.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute datename.