You can use dateadd function to add or subtract a number of units from any part (year, month, day, hour, minute, second, millisecond, etc…) of a datetime variable.
DateAdd(Date Part, Number Of Units To Add, Source Date)
Date Part : –
Specify which part of the date you want to increment or decrement (i.e. Year, Quarter, Months, Minute, etc…)
Set of parts you can use:
Year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond.
Number Of Units To Add: –
Specify by how many number of date parts you want to add to the source date.
Source Date: –
As obvious it is, this is where you specify the source date to which you want to add or subtract values.
The function doesn’t take the source date by reference so it will not alter the source date. If you want to alter the source date then you have to assign the result of the function to the source date variable.
Example : –
Following code will subtract 90 days from the source date. Note the – sign used with the in the dateadd function to illustrate use of – sign.
declare @alteredDate datetime declare @sourceDate datetime declare @length int set @length = 90 set @sourceDate = '1 mar 2012' set @alteredDate = dateadd(day,-@length,@sourceDate) print 'Source Date = ' + cast(@sourceDate as nvarchar(50)) print 'Altered Date = ' + cast(@alteredDate as nvarchar(50))
// Output Source Date = Mar 1 2012 12:00AM Altered Date = Dec 2 2011 12:00AM
For an in-depth description visit this msdn page