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 employees
|
Results
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 subtracing a specific amount of time from a column or a date.
Did you find the page informational and useful? Share it using one of your favorite social sites.
Recommended Books & Training Resources