to get only Saturdays between two dates in plsql-CodePudding

SELECT * FROM dummy;

act_date
---------
27-JAN-22

SELECT * FROM dummy1;

rpt_date
---------
10-JAN-22
10-DEC-21

how to get only Saturdays between act_date of dummy and MIN(rpt_date) of dummy1 table in Oracle's SQL?

please help

CodePudding user response：

Here is a simple example of how to list all saturdays between 2 dates, you should be able to convert this to your data model.

WITH dummy(start_date, end_date) AS
(
  SELECT DATE'2014-01-30', DATE'2014-02-25'   FROM dual
), dummy_all_dates(dt) AS
(
  SELECT
    e.dt
    FROM
    dummy d CROSS APPLY 
    ( SELECT
        d.start_date   level - 1 AS dt
        FROM dual CONNECT BY level < d.end_date - d.start_date
    ) e
)
SELECT dt FROM dummy_all_dates 
WHERE TO_CHAR(dt,'FMDAY','NLS_DATE_LANGUAGE=english') = 'SATURDAY';

01-FEB-2014
08-FEB-2014
15-FEB-2014
22-FEB-2014

CodePudding user response：

Here's another method to get a years worth of Saturdays. Adjust the dates as needed.

The to_char function has various format masks you can use to extract the day-of-week from a date. This uses day to get the full day name:


with rws as (
  select date'2021-12-31'   level dt
  from   dual
  connect by level <= (
    date'2022-01-01' - date'2021-01-01'
  )
)
  select dt
  from   rws
  where  to_char ( 
    dt, 
    'fmday', 
    'nls_date_language = English' 
  ) = 'saturday';