Export (0) Print
Expand All
Expand Minimize
14 out of 17 rated this helpful - Rate this topic

DATENAME (Transact-SQL)

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). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.

Topic link iconTransact-SQL Syntax Conventions


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.

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.

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.

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

The default hour-minute-second for the time data type is 00:00:00. The following statement has time part arguments for datepart, a date argument for date, and returns 0, 0, 0.

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. In SQL Server 2008, DATENAME implicitly casts string literals as datetime2 types. When using DATENAME with DATEADD, avoid implicit casts of string literals. For more information, see DATEADD (Transact-SQL)

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.

datepart

Return value

year, yyyy, yy

2007

quarter, qq, q

4

month, mm, m

October

dayofyear, dy, y

303

day, dd, d

30

week, wk, ww

44

weekday, dw

Tuesday

hour, hh

12

minute, n

15

second, ss, s

32

millisecond, ms

123

microsecond, mcs

123456

nanosecond, ns

123456700

TZoffset, tz

310

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.