The DATEPART function is used in Microsoft SQL Server to return a single part of a date/time, such as year, month, day, etc.
Employees Table
employeeID | employeeName | hireDate |
|---|---|---|
| 1000 | John Smith | 1995-12-03 |
| 1001 | Fred White | 2001-10-12 |
| 1002 | Jane Scott | 1998-05-01 |
| 1003 | Samuel Williams | 1991-01-03 |
In this example, we want to find out what is the employees’ year of hire.
Syntax
DATEPART(datepart, column_name)DATEPART Reference Table
| datepart | abbreviation |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | d |
| week | wk, ww |
| weekday | dw, w |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
| TZoffset | tz |
| ISO_WEEK | isowk, isoww |
Example
SELECT employeeName as [Employee Name], DATEPART(year,hireDate) as [Year Hired]
FROM employeesResults
| Employee Name | Year Hired |
|---|---|
| John Smith | 1995 |
| Fred White | 2001 |
| Jane Scott | 1998 |
| Samuel Williams | 1991 |
The SQL DATEPART function can be very useful when you are interested in formatting the results using a specific format of a date and/or time.
