Logo 
Search:

SQL Server Articles

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

DATEPART Function

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

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

DATEPART function is used to get part of the specified date.

Syntax of DATEPART Function :


DATEPART ( 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 DATEPART function is int.

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

Date Part

Abbreviation

Return Values

Example

year

yy

1840-9999

DATEPART(yy,GETDATE()) 2002

quarter

qq

1-4

DATEPART(qq,GETDATE())      3

month

mm

1-12

DATEPART(mm,GETDATE())   8

week

wk

1-53

DATEPART(wk,GETDATE())   31

weekday

dw

1-7 (Sunday,...,Saturday)

DATEPART(dw,GETDATE())     2

dayofyear

dy

1-366

DATEPART(dy,GETDATE())  214

day

dd

1-31

DATEPART(dd,GETDATE())     2

hour

hh

0-23

DATEPART(hh,GETDATE())   14

minute

mi

0-59

DATEPART(mi,GETDATE())     4

second

0-59

DATEPART(ss,GETDATE())    34

millisecond

ms

0-99 (with precision of 2)

DATEPART(ms,GETDATE())   30






Examples of DATEPART Function :

Example 1 : Use of DATEPART function in SELECT clause

SELECT DATEPART(WEEKDAY, '10/02/2002')
Output
4
 
Above examples shows that 10/02/2010 (format : MM/DD/YYYY) is the 4th day of week for the specified date.
 


Example 2 : Use of DATEPART function in SELECT clause to display year of table field

SELECT ShipName, OrderDate, DATEPART(YEAR, OrderDate) AS 'Year'
FROM   Orders

Output
ShipName                            OrderDate                        Year
Vins et alcools Chevalier    1996-07-04 00:00:00.000      1996
Toms Spezialitäten             1996-07-05 00:00:00.000      1996
Hanari Carnes                    1996-07-08 00:00:00.000      1996
Victuailles en stock            1996-07-08 00:00:00.000      1996
Suprêmes délices               1996-07-09 00:00:00.000      1996
 
Above example displays a year of order date field.
  

 

Example 3 : Use of DATEPART function in WHERE clause

SELECT  ShipName, OrderDate
FROM     Orders
WHERE  DATEPART(MONTH, OrderDate) > 7

Output
ShipName                                      OrderDate
Wartian Herkku                              1996-08-01 00:00:00.000
Split Rail Beer & Ale                      1996-08-01 00:00:00.000
Rattlesnake Canyon Grocery          1996-08-02 00:00:00.000
QUICK-Stop                                 1996-08-05 00:00:00.000                            
 
Above example displays all orders having month grater than 7th.                                                      
 

  
Share: 

 
 
 

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

Sarita Patel
Sarita Patel author of DATEPART 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!