Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Adding and Removing Days on Date and Time Values

By: FYIcenter.com

(Continued from previous topic...)

How To Add or Remove Days on Date and Time Values?

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

(Continued on next topic...)

1. How To Add or Remove Days on Date and Time Values?
2. Can Date and Time Values Be Converted into Integers?
3. Can Integers Be Converted into Date and Time Values?
4. Are DATETIME and NUMERIC Values Convertible?
5. Can a DATETIME Value Be Subtracted from Another DATETIME Value?
6. What Are the Date and Time Functions Supported by SQL Server 2005?
7. How To Increment or Decrement Parts of DATETIME Values?
8. How To Use DATEADD() Function?
9. How To Calculate DATETIME Value Differences Using the DATEDIFF() Function?
10. How To Calculate Age in Days, Hours and Minutes?
11. How To Get Month and Weekday Names from DATETIME Values?
12. How To Get Parts of DATETIME Values as Integers?
13. How To Get Year, Month and Day Out of DATETIME Values?
14. What Is the Difference Between GETDATE() and GETUTCDATE()?
15. How To Format Time Zone in +/-hh:mm Format?
16. How To Format DATETIME Values to Strings with the CONVERT() Function?
17. How To Truncate DATETIME Values to Dates without Time?
18. How To Set Different Parts of a DATETIME Value?