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.