IntroductionThe 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 SyntaxDATEADD(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 Hopes help.thank you for reading. |