How To Add or Remove Days on Date and Time Values in SQL Server Transact-SQL?

SQL Server 2005 only support two operators on data and time values:

• + (Add): Adding days to a date and time value.
• - (Subtract): Removing days from a date and time value.

Note that to add or subtract days on a date and time value, the other operand must be a numeric value: an integer or a decimal number.

Here are some good examples of adding days to and subtracting days from date and time values:

-- Leap year is supported
DECLARE @birth_date DATETIME;
SET @birth_date = '29-Feb-2000 03:00:00.000';
SELECT @birth_date + 1;
SELECT @birth_date - 1;
GO
2000-03-01 03:00:00.000
2000-02-28 03:00:00.000

-- Partial days are supported
DECLARE @birth_date DATETIME;
SET @birth_date = '29-Feb-2000 03:00:00.000';
SELECT @birth_date + 0.5; -- adding a half day
SELECT @birth_date + 1.0/3;
GO
2000-03-01 15:00:00.000
2000-02-29 10:59:59.970

⇒ Converting Date and Time Values into Integers in SQL Server

⇐ Verify Time Precision in SQL Server Transact-SQL

⇑ Date/Time Operations and Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials