to get only Tuesdays between the two days in plsql

Question

how to get only Tuesdays using below query

with range1 as(
select min(active_date) as start_Date
,min(r_date) as end_date,a.id
  from dummy a, dummy1 b
where 
and a.id=b.id group by a.id)
select start_Date+level-1 day1,deal_id
from range1
connect by level <=(
trunc(end_Date)-trunc(start_Date)+1)

Answer 1

Here's one option:

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> alter session set nls_date_format = 'dd.mm.yyyy, fmDay';

Session altered.

SQL> with range (start_date, end_date) as
  2    (select date '2022-02-01',
  3            date '2022-03-01'
  4     from dual
  5    ),
  6  all_days as
  7    (select start_date + level - 1 as datum
  8     from range
  9     connect by level <= end_date - start_date + 1
 10    )
 11  select datum
 12  from all_days
 13  where to_char(datum, 'fmDay') = 'Tuesday';

DATUM
---------------------
01.02.2022, Tuesday
08.02.2022, Tuesday
15.02.2022, Tuesday
22.02.2022, Tuesday
01.03.2022, Tuesday

SQL>

Answer 2

Using SYSDATE :-

SELECT * FROM (SELECT TRUNC(SYSDATE,'MM') + LEVEL - 1 AS Month_Date, 
    to_char(TRUNC(SYSDATE,'MM') + LEVEL - 1,'Day') AS Tue
    FROM DUAL 
    CONNECT BY TRUNC((TRUNC(SYSDATE,'MM') + LEVEL - 1),'MM') = TRUNC(SYSDATE,'MM') )
    WHERE trim(Tue) = 'Tuesday';

Using any give date :-

WITH date_range AS 
(
    SELECT TO_DATE('01-03-2022','dd-mm-yyyy') fdt, TO_DATE('31-03-2022','dd-mm-yyyy') ldt from dual
),
All_Dates AS 
(SELECT TRUNC(fdt,'MM') + LEVEL -1 AS Month_date ,
          to_char(TRUNC(fdt,'MM') + LEVEL - 1,'Day') AS Tue
FROM date_range
CONNECT BY LEVEL <= ldt - fdt + 1 )
SELECT Month_date,Tue FROM All_Dates WHERE trim(TUE) = 'Tuesday';