Get Current Week/Month Dates-CodePudding

I want to make function get dates of current week. I will pass week start date number dynamically.Week start date can change.It can be sunday or monday or any day.I shoud be able to pass week start date number dynamically.

If i Call function GetCurrentWeekDates(weekStartDay NUMBER,callingday Date),i should get the result like the following

GetCurrentWeekDates(1,'10/04/2022') --1 is sunday

Result should be as following

and when GetCurrentWeekDates(1,11/04/2022) --1 is sunday

Result should be as following

and when GetCurrentWeekDates(1,'14/04/2022') --1 is sunday

Result should be as following

14/04/2022
15/04/2022
16/04/2022

Similarly

I want to make function to get dates of current month. I will pass month number dynamically

If i Call function GetCurrentMonthDates(monthNumber NUMBER,callingday Date),i should get the result like the following

GetCurrentMonthDates(4,'01/04/2022') 4 is April

Result should be as following

01/04/2022
02/04/2022
.
.
30/04/2022

GetCurrentMonthDates(4,'16/04/2022') 4 is April

Result should be as following

16/04/2022
17/04/2022
.
.
30/04/2022

CodePudding user response：

Here's a week option; I'll let you write the month function yourself (shouldn't be too difficult, now that you know how).

SQL> create or replace function f_week (par_start_day in varchar2, par_datum in date)
  2    return sys_refcursor
  3  is
  4    -- PAR_START_DAY: mon, tue, ..., sat, sun
  5    rc sys_refcursor;
  6  begin
  7    open rc for
  8      select par_datum   level - 1
  9      from dual
 10      connect by level <= next_Day(par_datum, par_start_day) - par_datum;
 11    return rc;
 12  end;
 13  /

Function created.

Testing:

SQL> select f_week('sun', date '2022-04-10') from dual;

F_WEEK('SUN',DATE'20
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

:B1 LEVEL-1
---------------
10.04.2022, sun
11.04.2022, mon
12.04.2022, tue
13.04.2022, wed
14.04.2022, thu
15.04.2022, fri
16.04.2022, sat

7 rows selected.

Some more testing:

SQL> select f_week('sun', date '2022-04-14') from dual;

F_WEEK('SUN',DATE'20
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

:B1 LEVEL-1
---------------
14.04.2022, thu
15.04.2022, fri
16.04.2022, sat


SQL>