Home > Articles > Data

  • Print
  • + Share This
From the author of

Subtracting and Adding

All DBMSs allow the subtraction of a date from a date. By default, the result is an integer representing the number of result days. For example, March 1 2000 minus February 28 2000 returns 2.

The SQL Standard actually demands more complexity. It defines two new data types: year-month INTERVAL and day INTERVAL (there are other INTERVAL variants, but they are for differences between two times or between two timestamps). So, with standard SQL, a subtraction results in either a number of years/months or a number of days, but never a combination of the two. Informix, Ingres, and Oracle have some support for the INTERVAL data types.

DB2 tries to allow for subtractions that result in more complex "duration" values. For example, March 31 2000 minus February 28 2000 returns a duration value telling us that the difference is one month and three days. This is bold because there can be difficulties deciding on the order of execution and the end of the month.

Adding months to dates can be troublesome. Should March 31 plus 1 month be an error, should it result in April 30, or should it result in May 1? The consensus is that the answer is April 30. Some DBMSs allow the calculation but return a warning. The SQL Standard says the result should be an error.

  • + Share This
  • 🔖 Save To Your Account