The DATEDIFF function is used in Microsoft SQL Server to return the time between two dates/date columns.
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 how many days an employee has been working for the organization since their hire date.
Syntax
DATEDIFF(datepart, startDate, endDate)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],
DATEDIFF(day,hiredate,GETDATE()) as [Total Days Since Hire]
FROM employeesResults (Query ran on 12/13/2011)
| Employee Name | Total Days Since Hire |
|---|---|
| John Smith | 5854 |
| Fred White | 3714 |
| Jane Scott | 4974 |
| Samuel Williams | 7649 |
The SQL DATEDIFF function can be very useful when you are interested in finding out how much time there is between two dates, a date, and a date field, or two date fields.
