SQL DATENAME Function

DATENAME returns a string with a specified datepart from a given date.

Dateparts include year, month, day, minute, and others (values listed below).

The returned string value is often numeric, for example year and minute dateparts.

Example

#

This example returns the month name from a date.

SELECT DATENAME(month, '2022-01-18') as Month
Result:  1 record
Month
January

Using DATENAME

#

A common use is to get a datepart from the current date.
In this example the current weekday is returned.

SELECT DATENAME(weekday, GETDATE()) as Today
Result:  1 record
Today
Thursday

Syntax

Syntax of the DATENAME function .

DATENAME(date_part, input_date) 

date_part -- the part of date requested (datepart values are listed below).

input_date -- a date from which to extract the datepart.

These are valid date_part values and possible abbreviations

Date part Abbreviation
year yyyy or yy
quarter qq or q
month mm or m
dayofyear dy or y
day dd or d
week wk or ww
weekday dw
hour hh
minute mi or n
second ss or s
millisecond ms
microsecond mcs
nanosecond ns

More Examples

DATENAME. with WEEKDAY.

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List orders and the day-of-week they were placed.
SELECT FirstName, LastName, OrderNumber, TotalAmount,
       DATENAME(dw, OrderDate) AS 'Weekday'
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
Result:  830 records
FirstName LastName OrderNumber TotalAmount Weekday
Paul Henriot 542378 440.00 Wednesday
Karin Josephs 542379 1863.40 Thursday
Mario Pontes 542380 1813.00 Sunday
Mary Saveley 542381 670.80 Sunday
Pascale Cartrain 542382 3730.00 Monday

DATENAME with QUARTER.

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all orders placed in the third quarter of 2013.
 SELECT FirstName, LastName, OrderNumber, TotalAmount
   FROM [Order] O
   JOIN Customer C ON O.CustomerId = C.Id
  WHERE YEAR(OrderDate) = 2013 
    AND DATENAME(quarter, OrderDate) = 3
Result:  103 records
FirstName LastName OrderNumber TotalAmount
Paula Parente 542715 142.50
Maurizio Moroni 542716 28.00
Bernardo Batista 542717 807.38
Horst Kloss 542718 3900.00
Howard Snyder 542719 72.00

You may also like



Guides


vsn 3.1