Did you find this useful?
Socialize it today.


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.

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

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