Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p><strong>(StartA &lt;= EndB) and (EndA >= StartB)</strong></p> <p><em>Proof:</em><br> Let ConditionA Mean that DateRange A Completely After DateRange B<br> <code>_ |---- DateRange A ------| |---Date Range B -----| _</code><br> (True if <code>StartA &gt; EndB</code>) </p> <p>Let ConditionB Mean that DateRange A is Completely Before DateRange B<br> <code>|---- DateRange A -----| _ _ |---Date Range B ----|</code><br> (True if <code>EndA &lt; StartB</code>) </p> <p>Then Overlap exists if Neither A Nor B is true -<br> (If one range is neither completely after the other,<br> nor completely before the other, then they must overlap.)</p> <p>Now one of <a href="https://en.wikipedia.org/wiki/De_Morgan%27s_laws" rel="noreferrer">De Morgan's laws</a> says that: </p> <p><code>Not (A Or B)</code> &lt;=> <code>Not A And Not B</code></p> <p>Which translates to: <code>(StartA &lt;= EndB) and (EndA &gt;= StartB)</code></p> <hr> <p>NOTE: This includes conditions where the edges overlap exactly. If you wish to exclude that,<br> change the <code>&gt;=</code> operators to <code>&gt;</code>, and <code>&lt;=</code> to <code>&lt;</code> </p> <hr> <p>NOTE2. Thanks to @Baodad, see <a href="http://baodad.blogspot.com/2014/06/date-range-overlap.html" rel="noreferrer">this blog</a>, the actual overlap is least of:<br> { <code>endA-startA</code>, <code>endA - startB</code>, <code>endB-startA</code>, <code>endB - startB</code> } </p> <p><code>(StartA &lt;= EndB) and (EndA &gt;= StartB)</code> <code>(StartA &lt;= EndB) and (StartB &lt;= EndA)</code></p> <hr> <p>NOTE3. Thanks to @tomosius, a shorter version reads:<br> <code>DateRangesOverlap = max(start1, start2) &lt; min(end1, end2)</code><br> This is actually a syntactical shortcut for what is a longer implementation, which includes extra checks to verify that the start dates are on or before the endDates. Deriving this from above: </p> <p>If start and end dates can be out of order, i.e., if it is possible that <code>startA &gt; endA</code> or <code>startB &gt; endB</code>, then you also have to check that they are in order, so that means you have to add two additional validity rules:<br> <code>(StartA &lt;= EndB) and (StartB &lt;= EndA) and (StartA &lt;= EndA) and (StartB &lt;= EndB)</code> or:<br> <code>(StartA &lt;= EndB) and (StartA &lt;= EndA) and (StartB &lt;= EndA) and (StartB &lt;= EndB)</code> or,<br> <code>(StartA &lt;= Min(EndA, EndB) and (StartB &lt;= Min(EndA, EndB))</code> or:<br> <code>(Max(StartA, StartB) &lt;= Min(EndA, EndB)</code> </p> <p>But to implement <code>Min()</code> and <code>Max()</code>, you have to code, (using C ternary for terseness),:<br> <code>(StartA &gt; StartB? Start A: StartB) &lt;= (EndA &lt; EndB? EndA: EndB)</code> </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