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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: