For SQLServer using arthemetic and DATEADD one can modify date

select getdate() -- today
, getdate() + 1 -- tomorrow
, getdate() – 2 -- day before yesterday
, DATEADD(hh, 1, getdate()) -- add 1 hour
, DATEADD(mi, 30, getdate()) -- add 30 minutes
, DATEADD(d, 7, getdate()) -- add next week
, DATEADD(d, -7, getdate()) -- last week
, DATEADD(yyyy, 1, getdate()) -- add next year

Output:
2011-02-11 00:07:18.990 2011-02-12 00:07:18.990 2011-02-09 00:07:18.990 2011-02-11 01:07:18.990 2011-02-11 00:37:18.990 2011-02-18 00:07:18.990 2011-02-04 00:07:18.990 2012-02-11 00:07:18.990 2011-02-11 01:07:18.930