StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>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).</p> <p>In the first case, we must truncate both dates to their year and month values:</p> <pre><code>SELECT TRUNC( TO_DATE('2011-04-22','yyyy-mm-dd'), 'mm') as trunc_date FROM dual; </code></pre> <p>gives:</p> <pre><code> trunc_date ---------- 2011-04-01 </code></pre> <p>In the second case we don't have to modify the dates.</p> <p>At least two approaches can be used to solve the initial problem:</p> <p>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.</p> <pre><code>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' ) ; </code></pre> <p>Note the truncated dates. Leaving this out will require a perfect day to day match between dates.</p> <p>The second approaches is based on calculating the difference in months between two dates and picking a calculation that gives a 1 month difference.</p> <pre><code>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 </code></pre> <p>The order of the fields in months_between is important here. In the provided example:</p> <ul> <li>for b.date_field one month <strong>ahead</strong> of a.date_field the value is 1</li> <li>for b.date_field one month <strong>before</strong> a.date_field the value is -1 (negative one)</li> </ul> <p>Reversing the order will also reverse the results.</p> <p>Hope this answers your question.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload