SQL Server DATEADD Function Example to Add or Subtract Dates

Introduction: 

In this article I will explain how to use DATEADD function to add or subtract specified time interval from dates in SQL Server.

Description:

In previous articles I explained Substring function Example, SQL Query to remove first and last character from string and many articles relating to SQL Server. Now I will explain how to use DATEADD function to add or subtract specified time interval from dates in SQL Server.

DATEADD function:

This function is used to add or subtract specified time interval from dates in SQL Server. Generally DATEADD function will take 3 arguments.

Declaration of DATEADD function:

DATEADD (datepart, number, date)

In this function

1^st Argument "datepart" is the interval type we need to add or subtract for example day, month, year, hour, minute, second.

datepart can be one of the following:

datepart	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

2^nd Argument the number is the amount of datepart units to add or subtract. For example if datepart was d and the number was 3 then it would add three days to the date specified.

3^rd Argument date is the date to add/subtract the number of dateparts from. It can either be a column in the database, a constant value or a function such as GETDATE() which would return the current date and time.

Examples of DATEADD Function:

Ex: 1

Suppose if we want to add one month to present date we need to write the query like this

SELECT GETDATE() AS CurrentDate,DATEADD (mm, 1, GETDATE()) AS NewDate

Output:

CurrentDate	NewDate
2012-10-31 03:10:19.457	2012-11-30 03:10:19.457

Ex: 2

Suppose if we want to subtract one month from present date we need to write the query like this

SELECT GETDATE() AS CurrentDate,DATEADD (mm, -1, GETDATE()) AS NewDate

Output:

CurrentDate	NewDate
2012-10-31 03:14:30.200	2012-09-30 03:14:30.200

Ex: 3

We have Orders table like this

OrderId	OrderName	ShippingDate
1	Aspdotnet-Suresh Book	2012-10-31 03:44:00.053

Now I need to add 15 days to “ShippingDate” for that we need to write the query like this

SELECT OrderId, OrderName, DATEADD(dd,15,ShippingDate) As ShippingDate FROM Orders

Output:

OrderId	OrderName	ShippingDate
1	Aspdotnet-Suresh Book	2012-11-15 03:44:00.053

This way we can use DATEADD function to add or subtract dates from specified time interval in SQL Server.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email