Finding the days of the week within a date range using oracle SQL

Question

Suppose the following table structure:

Event: 
  id: integer
  start_date: datetime
  end_date: datetime

Is there a way to query all of the events that fall on a particular day of the week? For example, I would like to find a query that would find every event that falls on a Monday. Figuring out if the start_date or end_date falls on a Monday, but I'm not sure how to find out for the dates between.

Pure SQL is preferred since there is a bias against stored procedures here, and we're calling this from a Rails context which from what I understand does not handle stored procedures as well.

Answer 1

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
        )

The subquery iterates all days from start_date to end_date, checks each day, and if it's a Monday, returns 1.

You can easily extend this query for more complex conditions: check whether an event falls on ANY Monday OR Friday 13th, for instance:

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
        )

Note that I use MOD(start_date - TO_DATE(1, 'J') + level - 1, 7) instead of TO_CHAR('D'). This is because TO_CHAR('D') is affected by NLS_TERRITORY and should not be used for checking for a certain day of week.

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 Monday.

Even if the intervals are 1 day long, the index will return 14% of values, if intervals are longer, even more.

Since INDEX SCAN would be inefficient in this case, and the inner subquery is very fast (it uses in-memory FAST DUAL access method), this, I think, will be an optimal method, both by efficiency and extensibility.

See the entry in my blog for more detail:

• Checking event dates

Answer 2

This should do it more simply:

select *
from event
where 2 between to_number(trim(to_char(start_date,'D')))
            and to_number(trim(to_char(end_date,'D')))
   or (end_date - start_date) > 6