Oracle PL/SQL : Return date based on the day for sysdate in oracle-CodePudding

I have a requirement where i have to pick data from DB based on start_date and end_date passed to it.

If sysdate is sunday then function should return start_date as date on saturday in YYYYMMDD format and end_date as date on saturday in YYYYMMDD format.
If sysdate is monday then start_date should be date on saturday and end_date should be of sunday.
If sysdate is tuesday then start_date should be date on saturday and end_date will be of monday..and so on..

How will a function look like to return two dates in YYYYMMDD format based on req.

CodePudding user response：

It appears that start date is always Saturday, while end date is a day previous to sysdate.

If that's so, here's how:

My database speaks Croatian so I'm switching to English (and setting default date format, just to show what sysdate is); you don't have to do that.

SQL> alter session set nls_date_language = 'english';

Session altered.

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

Session altered.

The TEMP CTE mimics sysdate change. next_day function searches for Saturday previous to sysdate.

21.11.2021 is Sunday, so you want to get 20.11.2021 as start and end dates:

SQL> with temp as (select date '2021-11-21' sys_date from dual)
  2  select
  3    sys_date,
  4    to_char(next_day(sys_date, 'SATURDAY') - 7, 'yyyymmdd') as start_date,
  5    to_char(sys_date - 1, 'yyyymmdd') as end_date
  6  from temp;

SYS_DATE   START_DA END_DATE
---------- -------- --------
21.11.2021 20211120 20211120

23.11.2021 is Tuesday, so you want to get Saturday as start date and Monday as end date:

SQL> with temp as (select date '2021-11-23' sys_date from dual)
  2  select
  3    sys_date,
  4    to_char(next_day(sys_date, 'SATURDAY') - 7, 'yyyymmdd') as start_date,
  5    to_char(sys_date - 1, 'yyyymmdd') as end_date
  6  from temp;

SYS_DATE   START_DA END_DATE
---------- -------- --------
23.11.2021 20211120 20211122

SQL>

In reality, you'd just

SQL> select
  2    sysdate,
  3    to_char(next_day(sysdate, 'SATURDAY') - 7, 'yyyymmdd') as start_date,
  4    to_char(sysdate - 1, 'yyyymmdd') as end_date
  5  from dual;

SYSDATE    START_DA END_DATE
---------- -------- --------
18.11.2021 20211113 20211117

SQL>