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

Question

Assume the following table structure:

Event: 
  id: integer
  start_date: datetime
  end_date: datetime

Is there a way to request all events that occur on a particular day of the week? For example, I would like to find a query that will find every event that falls on Monday. Find out if the tags fall in start_dateor end_dateon Monday, but I'm not sure how to find out the dates between them.

Pure SQL is preferable since there is an offset against stored procedures, and we call it from the Rails context, which, from what I understand, also does not process stored procedures.

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
        )

start_date end_date, , Monday, 1.

: , ANY Monday OR Friday 13th, :

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
        )

, TO_CHAR('D') MOD(start_date - TO_DATE(1, 'J') + level - 1, 7). , TO_CHAR('D') NLS_TERRITORY .

. , , Monday.

1 , 14%, , .

INDEX SCAN , ( FAST DUAL), , , , .

. :

Answer 2

:

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