Recipe8.1.Adding and Subtracting Days, Months, and Years

Recipe 8.1. Adding and Subtracting Days, Months, and Years

Problem

You need to add or subtract some number of days, months, or years from a date. For example, using the HIREDATE for employee CLARK you want to return six different dates: five days before and after CLARK was hired, five months before and after CLARK was hired, and, finally, five years before and after CLARK was hired. CLARK was hired on "09-JUN-1981", so you want to return the following result set:

 HD_MINUS_5D HD_PLUS_5D  HD_MINUS_5M HD_PLUS_5M  HD_MINUS_5Y HD_PLUS_5Y ----------- ----------- ----------- ----------- ----------- ----------- 04-JUN-1981 14-JUN-1981 09-JAN-1981 09-NOV-1981 09-JUN-1976 09-JUN-1986 12-NOV-1981 22-NOV-1981 17-JUN-1981 17-APR-1982 17-NOV-1976 17-NOV-1986 18-JAN-1982 28-JAN-1982 23-AUG-1981 23-JUN-1982 23-JAN-1977 23-JAN-1987 

Solution

DB2

Standard addition and subtraction is allowed on date values, but any value that you add to or subtract from a date must be followed by the unit of time it represents:

 1 select hiredate -5 day   as hd_minus_5D, 2        hiredate +5 day   as hd_plus_5D, 3        hiredate -5 month as hd_minus_5M, 4        hiredate +5 month as hd_plus_5M, 5        hiredate -5 year  as hd_minus_5Y, 6        hiredate +5 year  as hd_plus_5Y 7   from emp 8  where deptno = 10 

Oracle

Use standard addition and subtraction for days, and use the ADD_MONTHS function to add and subtract months and years:

 1 select hiredate-5                 as hd_minus_5D, 2        hiredate+5                 as hd_plus_5D, 3        add_months(hiredate,-5)    as hd_minus_5M, 4        add_months(hiredate,5)     as hd_plus_5M, 5        add_months(hiredate,-5*12) as hd_minus_5Y, 6        add_months(hiredate,5*12)  as hd_plus_5Y 7   from emp 8  where deptno = 10 

PostgreSQL

Use standard addition and subtraction with the INTERVAL keyword specifying the unit of time to add or subtract. Single quotes are required when specifying an INTERVAL value:

 1 select hiredate - interval '5 day'   as hd_minus_5D, 2        hiredate + interval '5 day'   as hd_plus_5D, 3        hiredate - interval '5 month' as hd_minus_5M, 4        hiredate + interval '5 month' as hd_plus_5M, 5        hiredate - interval '5 year'  as hd_minus_5Y, 6        hiredate + interval '5 year'  as hd_plus_5Y 7   from emp 8  where deptno=10 

MySQL

Use standard addition and subtraction with the INTERVAL keyword specifying the unit of time to add or subtract. Unlike the PostgreSQL solution, you do not place single quotes around the INTERVAL value:

 1 select hiredate - interval 5 day   as hd_minus_5D, 2        hiredate + interval 5 day   as hd_plus_5D, 3        hiredate - interval 5 month as hd_minus_5M, 4        hiredate + interval 5 month as hd_plus_5M, 5        hiredate - interval 5 year  as hd_minus_5Y, 6        hiredate + interval 5 year  as hd_plus_5Y 7   from emp 8  where deptno=10 

Alternatively, you can use the DATE_ADD function, which is shown below:

 1 select date_add(hiredate,interval -5 day)   as hd_minus_5D, 2        date_add(hiredate,interval  5 day)   as hd_plus_5D, 3        date_add(hiredate,interval -5 month) as hd_minus_5M, 4        date_add(hiredate,interval  5 month) as hd_plus_5M, 5        date_add(hiredate,interval -5 year)  as hd_minus_5Y, 6        date_add(hiredate,interval  5 year)  as hd_plus_5DY 7   from emp 8  where deptno=10 

SQL Server

Use the DATEADD function to add or subtract different units of time to/from a date:

 1 select dateadd(day,-5,hiredate)   as hd_minus_5D, 2        dateadd(day,5,hiredate)    as hd_plus_5D, 3        dateadd(month,-5,hiredate) as hd_minus_5M, 4        dateadd(month,5,hiredate)  as hd_plus_5M, 5        dateadd(year,-5,hiredate)  as hd_minus_5Y, 6        dateadd(year,5,hiredate)   as hd_plus_5Y 7   from emp 8  where deptno = 10 

Discussion

The Oracle solution takes advantage of the fact that integer values represent days when performing date arithmetic. However, that's true only of arithmetic with DATE types. Oracle9 i Database introduced TIMESTAMP types. For those, you should use the INTERVAL solution shown for PostgreSQL. Beware too, of passing TIMESTAMPs to old-style date functions such as ADD_MONTHS. By doing so, you can lose any fractional seconds that such TIMESTAMP values may contain.

The INTERVAL keyword and the string literals that go with it represent ISO-standard SQL syntax. The standard requires that interval values be enclosed within single quotes. PostgreSQL (and Oracle9 i Database and later) complies with the standard. MySQL deviates somewhat by omitting support for the quotes.