Oracle add_months Tips

Oracle Database Tips by Donald Burleson

The add_months Oracle date function gives you the same day, n number of months away. Note that the n can be positive or negative, so you can go backwards in time with add_months. Using nls_date_format, add_months is one of the most powerful Oracle commands for computing future months and years.

select
sysdate,
add_months(sysdate,1),
add_months(sysdate,2),
add_months(sysdate,3),
add_months(sysdate,4),
add_months(sysdate,5),
add_months(sysdate,6)
from
dual;

SYSDATE ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH
--------- --------- --------- --------- --------- --------- ---------
24-JAN-05 24-FEB-05 24-MAR-05 24-APR-05 24-MAY-05 24-JUN-05 24-JUL-05

select
sysdate,
add_months(sysdate,-1),
add_months(sysdate,-2),
add_months(sysdate,-3),
add_months(sysdate,-4),
add_months(sysdate,-5),
add_months(sysdate,-6)
from
dual;

SYSDATE ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH
--------- --------- --------- --------- --------- --------- ---------
24-JAN-05 24-DEC-04 24-NOV-04 24-OCT-04 24-SEP-04 24-AUG-04 24-JUL-04

The Oracle add_months SQL function is a great way to perform date manipulation. Here are some syntax examples of add_months:

NEXT_DAY(ADD_MONTHS(trunc(sysdate,'Q'),3),'SUNDAY')

add_months( sysdate-1 )

This add_months will return the first Monday of each quarter year:

'next_day(add_months(trunc(sysdate, "q"), 3), "monday")'
'next_day(add_months(trunc(systimestamp, "q"), 3), "monday")'

Here, add_months is used to give the last day of last month:

select
last_day(add_months(sysdate,-1))
from dual;

The Oracle documentation gives us the following example for returning "the month after the hire_date in the sample table employees" using add_months:

SELECT TO_CHAR(
     ADD_MONTHS(hire_date,1),
     'DD-MON-YYYY') "Next month"
     FROM employees
     WHERE last_name = 'Baer';

Next Month
-----------
07-JUL-1994

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

��