DATEADD function in Sql Server

Introduction

The DATEADD function is a powerful built-in function sql server to add dates in different way and types. So I would like to explore in this tip how we can use the DATEADD function for different purpose.

 DATEADD Function Syntax

DATEADD(datepart,number,date)

DatePart: Is the parameter that specifies on which part of the date to return a new value

Number: Is the value used to increment datepart

Date: Is an expression that returns a datetime or smalldatetime value

Supportable abbreviations for Datepart  

DatePart	Abbreviations
Year	yy,yyyy
Quarter	q,qq
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

 Examples:

DECLARE @Today DATETIME

SET @Today=GETDATE() --'13-Dec-2010'

SELECT DATEADD(Year, 5, @Today) AS dest_date  -- add 5 years from today date

------------Output-----------------------------

----------------------------------------------

--2015-12-13 11:53:21.707

SELECT DATEADD(quarter, 2, @Today) AS NewDate -- add 2 quarter with today date

------------Output-----------------------------

----------------------------------------------

--2011-06-13 11:55:38.033

SELECT DATEADD(Month, 3, @Today) AS NewDate -- add 4 months with current date 

------------Output-----------------------------

----------------------------------------------

--2011-03-13 11:57:23.267

SELECT DATEADD(dayofyear,10, @Today) AS NewDate -- add 10 days with day of year(current day)

------------Output-----------------------------

----------------------------------------------

--2010-12-23 11:57:56.453

SELECT DATEADD(Day, 5, @Today) AS NewDate -- add 5 days with today

------------Output-----------------------------

----------------------------------------------

--2010-12-18 11:57:56.453

SELECT DATEADD(Week, 2, @Today) AS NewDate -- add two weeks with today date

------------Output-----------------------------

----------------------------------------------

--2010-12-27 12:00:14.173

SELECT DATEADD(Weekday, 2, @Today) AS NewDate -- add two weekdays with today date

------------Output-----------------------------

----------------------------------------------

--2010-12-27 12:00:14.173

SELECT DATEADD(Hour, 7, @Today) AS NewDate -- add 7 hours with now date and time 

------------Output-----------------------------

----------------------------------------------

--2010-12-13 15:01:19.313

SELECT DATEADD(minute, 6, @Today) AS NewDate -- add 6 minutes with current time

------------Output-----------------------------

----------------------------------------------

--2010-12-13 12:08:31.517

SELECT DATEADD(minute, 6.5, @Today) AS NewDate -- add 6.5 minutes with current time

------------Output-----------------------------

----------------------------------------------

--2010-12-13 12:08:31.517

-- Note : See i have aded 6 and 6.5 minutes with datepart minutes, but result is same. beucase sql server 6.5 will be 6

SELECT DATEADD(second, 15, @Today) AS NewDate -- add  15 seconds with curren time

------------Output-----------------------------

----------------------------------------------

--2010-12-13 12:33:31.517

SELECT DATEADD(millisecond, 300, @Today) AS NewDate -- add 300 milliseconds to current time

------------Output-----------------------------

----------------------------------------------

--2010-12-13 12:06:15.893

Have a nice day... 'N happy Coding :)