Adding or Subtracting a Date Time Variable in SQL Server Using DateAdd Function


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.

Syntax: –
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


This entry was posted in SQL Server and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>