SQL DATEADD Function

Sunday, December 18, 2011

tags SQL

The DATEADD function is used in Microsoft SQL Server to add or subtract a specified time interval from a date.

Employees Table

employeeIDemployeeNamehireDate
1000John Smith1995-12-03
1001Fred White2001-10-12
1002Jane Scott1998-05-01
1003Samuel Williams1991-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

datepartabbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
dayd
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
TZoffsettz
ISO_WEEKisowk, isoww


Example
SELECT employeeName as [Employee Name], DATEADD(year,30,hireDate) as [Eligible Retirement]
FROM employees


Results

Employee NameEligible Retirement
John Smith2025
Fred White2031
Jane Scott2028
Samuel Williams2021

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

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook