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’.
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.