Computers & ProgrammingBackend DevelopmentSQL

SQL DATEADD Function

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 subtracting a specific amount of time from a column or a date.

Leave a Comment

Your email address will not be published.

Scroll to Top