Adding one month to saved date(oracle)

I have a table A which co开发者_Go百科ntains a Date type attribute. I want to write a query to select the date in another table B with value one month after the value in A.Any one know how to do it in oracle?

uhm... This was the first hit on google:

http://psoug.org/reference/date_func.html

It seems you're looking for the "add_months" function.

You need to use the ADD_MONTHS function in Oracle.

http://www.techonthenet.com/oracle/functions/add_months.php

Additional info: If you want to use this function with today's date you can use ADD_MONTHS(SYSDATE, 1) to get one month from now.

The question is to select a date_field from table b where date_field of table b is one month ahead of a date_field in table a.

An additional requirement must be taken into consideration which is currently unspecified in the question. Are we interested in whole months (days of month not taken into consideration) or do we want to include the days which might disqualify dates that are one month ahead but only by a couple of days (example: a=2011-04-30 and b=2011-05-01, b is 1 month ahead but only by 1 day).

In the first case, we must truncate both dates to their year and month values:

SELECT TRUNC( TO_DATE('2011-04-22','yyyy-mm-dd'), 'mm') as trunc_date
   FROM dual;

gives:

  trunc_date
  ----------
  2011-04-01

In the second case we don't have to modify the dates.

At least two approaches can be used to solve the initial problem:

First one revolves around adding one month to the date_field in table a and finding a row in table b with a matching date.

SELECT b.date_field
  FROM tab_a as a
      ,tab_b as b
 WHERE ADD_MONTHS( TRUNC( a.date_field, 'mm' ), 1) = TRUNC( b.date_field, 'mm' )
  ;

Note the truncated dates. Leaving this out will require a perfect day to day match between dates.

The second approaches is based on calculating the difference in months between two dates and picking a calculation that gives a 1 month difference.

SELECT b.date_field
  FROM tab_a as a
      ,tab_b as b
 WHERE months_between( TRUNC( b.date_field, 'mm') , TRUNC(a.date_field, 'mm') ) = 1

The order of the fields in months_between is important here. In the provided example:

• for b.date_field one month ahead of a.date_field the value is 1
• for b.date_field one month before a.date_field the value is -1 (negative one)

Reversing the order will also reverse the results.

Hope this answers your question.