This page examine how SQL math works in Oracle, MySQL, and SQL Server databases. Each implements ANSI functions differently but it’s unfair to say which is in full compliance or not with the ANSI specifications. It is probably fair to say they’re all close if not in compliance, and leave the rest to their respective marketing departments.
SQL implementations differ on how you may use dates to perform basic math operations as dates. Some provide an intuitive and simple approach while others deliver the utility through the exclusivity of SQL functions.
SQL Date Math Examples ↓
Examples:
- Adding dates.
- Subtracting dates
- Adding intervals
- Subtracting intervals
Oracle
The natural of Oracle’s DATE data type model is simple. A DATE data type isn’t really a date alone. It is a time stamp. A scalar integer maps to a day or date, as of midnight. A real number has an integer and a fraction expressed to the right of the decimal point. A DATE data type supports real numbers, which means they can be anytime of the day or night.
A default format masks for the TO_DATE() function are the DD-MON-RR (relative 50 years forward or backward) or DD-MON-YYYY (absolute date). You can enter a time-stamp that’s as precise as hundredths of a second by using the ‘DD-MON-YYYY HH24:MI:SS’ format mask with the TO_DATE() function. The TO_DATE() function lets you convert a date string an Oracle DATE data type.
You get a DATE data type when you add or subtract a NUMBER data type from a DATE data type.
Adding a Day
When you add an integer to a date in Oracle, you return that original date plus a 24-hour period. The return value is a date-time period because that’s what an Oracle DATE data type is.
1
2
| SELECT TO_DATE('31-MAR-2010') + 1 AS "New Date"
FROM dual; |
SELECT TO_DATE('31-MAR-2010') + 1 AS "New Date"
FROM dual;
The query returns the following date:
New Date
---------
01-APR-10 |
New Date
---------
01-APR-10
