Using DATEADD To Increase Or Decrease DateTime Value On SQL Server

If you want to add or subtract some time from DateTime value, SQL Server has very useful DATEADD function.

For example, to add two days to SomeDate field, use this code:

SELECT DATEADD(dd, 2, SomeDate) FROM SomeTable;


To subtract time, just use negative value of second parameter.

Syntax of DATEADD function is:

DATEADD( TimeIntervalName, Value, FieldOrVariable )

In previous example, d is abbreviation for day. Here is complete list of interval names and their abbreviations that can be used with DATEADD function:

Nanosecond: ns
Microsecond: mcs
Millisecond:  ms
Second:  ss or s
Minute: mi or n
Hour: hh
WeekDay: dw or w
Week: wk or ww
Day: dd or d
DayOfYear: dy or y
Month:  mm or just m
Quarter:  qq or q
Year:  yy or yyyy

Note that Nanosecond and Microsecond can be used only on SQL Server 2008 or later.