Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Date Time FunctionsRSS Feeds

DATENAME Function

Posted By: Sarita Patel     Category: SQL Server     Views: 17638

This article explains DATENAME function with different examples in sql server.

DATENAME function is used to get string of the specified datepart of the specified date.

 

Syntax of DATENAME Function :

DATENAME ( datepart ,date )

Datepart specifies the part of the date to return.

Date is an expression that returns a datetime or smalldatetime value or a character string in a date format.

Return type of DATENAME function is  nvarchar.

Below is the table that specifies possible datepart parameters, abbreviation of it and return values with examples where current date is '08/02/2002'.

Date Part

Abbreviation

Return Values

Examples

Year

yy

1840-9999

DATENAME(yy,GETDATE()) 2002

Quarter

qq

1-4

DATENAME(qq,GETDATE()) 3

Month

mm

January,...December

DATENAME(mm,GETDATE()) August

Week

wk

1-53

DATENAME(wk,GETDATE())  32

Weekday

dw

Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

DATENAME(dw,GETDATE()) Monday

Dayofyear

dy

1-366

DATENAME(dy,GETDATE())  214

Day

dd

1-31

DATENAME(dd,GETDATE())  2

Hour

hh

0-23

DATENAME(hh,GETDATE())  9

Minute

mi

0-59

DATENAME(mi,GETDATE())  49

Second

0-59

DATENAME(ss,GETDATE())  5

millisecond

ms

0-99 (with precision of 2)

DATENAME(ms,GETDATE())  387

 


Example of DATENAME Function :

Example 1 : Use of DATENAME function in SELECT clause

SELECT DATENAME(MONTH, '08/02/2010')

Output

August


Above examples shows month name of 08/02/2010 (format : MM/DD/YYYY) date.

 


Example 2 : Use of DATENAME function in SELECT clause to display weekday of table field

SELECT ShipName, OrderDate, DATENAME(WEEKDAY, OrderDate) AS 'WeekDay'

FROM   Orders

Output

ShipName                            OrderDate                          WeekDay

Vins et alcools Chevalier    1996-07-04 00:00:00.000       Thursday

Toms Spezialitäten             1996-07-05 00:00:00.000       Friday

Hanari Carnes                     1996-07-08 00:00:00.000      Monday

Victuailles en stock            1996-07-08 00:00:00.000       Monday

Suprêmes délices                1996-07-09 00:00:00.000      Tuesday

 

Above example displays a week day of order date field.

 


Example 3 : Use of DATENAME function in WHERE clause

SELECT  ShipName, OrderDate

FROM     Orders

WHERE  DATENAME(QUARTER, OrderDate) = 2

Output

ShipName                                          OrderDate

Bottom-Dollar Markets                      1997-04-01 00:00:00.000

La maison d'Asie                              1997-04-02 00:00:00.000

Comércio Mineiro                             1997-04-02 00:00:00.000

Laughing Bacchus Wine Cellars         1997-04-03 00:00:00.000

Tradiçao Hipermercados                   1997-04-04 00:00:00.000      

                                          

Above example displays all orders made in 2nd quarter of month.                                          

  
Share: 

 
 
 

Didn't find what you were looking for? Find more on DATENAME Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of DATENAME Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!