The DATEADD function is used in Microsoft SQL Server to add or subtract a specified time interval from a date.
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 when an employee will be eligible for retirement, after 30 years of service.
Syntax
DATEADD(datepart, number to offset, column_name/date)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],
DATEADD(year,30,hireDate) as [Eligible Retirement]
FROM employeesResults
| Employee Name | Eligible Retirement |
|---|---|
| John Smith | 2025 |
| Fred White | 2031 |
| Jane Scott | 2028 |
| Samuel Williams | 2021 |
The SQL DATEADD function can be very useful when you are interested in adding or subtracting a specific amount of time from a column or a date.
