StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>SELECT * FROM event WHERE EXISTS ( SELECT 1 FROM dual WHERE MOD(start_date - TO_DATE(1, 'J') + level - 1, 7) = 6 CONNECT BY level <= end_date - start_date + 1 ) </code></pre> <p>The subquery iterates all days from <code>start_date</code> to <code>end_date</code>, checks each day, and if it's a <code>Monday</code>, returns <code>1</code>.</p> <p>You can easily extend this query for more complex conditions: check whether an event falls on <code>ANY Monday OR Friday 13th</code>, for instance:</p> <pre><code>SELECT * FROM event WHERE EXISTS ( SELECT 1 FROM dual WHERE MOD(start_date - TO_DATE(1, 'J') + level - 1, 7) = 6 OR (MOD(start_date - TO_DATE(1, 'J') + level - 1, 7) = 3 AND TO_CHAR(start_date + level - 1, 'DD') = '13') CONNECT BY level <= end_date - start_date + 1 ) </code></pre> <p>Note that I use <code>MOD(start_date - TO_DATE(1, 'J') + level - 1, 7)</code> instead of <code>TO_CHAR('D')</code>. This is because <code>TO_CHAR('D')</code> is affected by <code>NLS_TERRITORY</code> and should not be used for checking for a certain day of week.</p> <p>This query does not use any indexes and always performs a full table scan. But this is not an issue in this specific case, as it's highly probable that a given interval will contain a <code>Monday</code>.</p> <p>Even if the intervals are <code>1</code> day long, the index will return <code>14%</code> of values, if intervals are longer, even more.</p> <p>Since <code>INDEX SCAN</code> would be inefficient in this case, and the inner subquery is very fast (it uses in-memory <code>FAST DUAL</code> access method), this, I think, will be an optimal method, both by efficiency and extensibility.</p> <p>See the entry in my blog for more detail:</p> <ul> <li><a href="http://explainextended.com/2009/05/07/checking-event-dates/" rel="nofollow noreferrer"><strong>Checking event dates</strong></a></li> </ul>

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