to get only Tuesdays between the two days in plsql-CodePudding

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)

CodePudding user response：

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>

CodePudding user response：

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';