By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
alter session set nls_date_format = 'yyyy-mm-dd';
with holiday_calendar ( h_date ) as
(Select date '2021-01-01' from dual union all
Select date '2021-10-18' from dual union all
Select date '2021-02-15' from dual union all
Select date '2021-05-31' from dual union all
Select date '2021-07-04' from dual union all
Select date '2021-09-06' from dual union all
Select date '2021-10-11' from dual union all
Select date '2021-11-11' from dual union all
Select date '2021-11-25' from dual union all
Select date '2021-12-25' from dual
) --select * from holiday_calendar;
, month_business_calendar (dom, bus_day_num) as
( select case when dom <= last_day(sysdate) --for_date_in)
then dom
else null
end
, row_number() over(order by dom)
from (select trunc(sysdate ,'mon') + (level-1) dom
from dual connect by level <= 31
)
where dom <= last_day(trunc(sysdate))
and to_char(dom,'dy', 'NLS_DATE_LANGUAGE=AMERICAN') not in ('sat','sun')
and trunc(dom) not in (select h_date from holiday_calendar)
order by dom
)
select dom "5th Business Day"
from month_business_calendar
where bus_day_num = 5;
| 5th Business Day |
|---|
| 2021-09-08 |
create or replace type monthly_business_day_calendar_typ is object
( day_of_month date
, business_day_num integer
) ;
create type monthly_business_day_calendar_array
is table of monthly_business_day_calendar_typ;
create or replace
function make_monthly_business_calendar(date_in date)
return monthly_business_day_calendar_array
is
monthly_business_day_calendar monthly_business_day_calendar_array;
begin
with holiday_calendar ( h_date ) as
(Select date '2021-01-01' from dual union all
Select date '2021-10-18' from dual union all
Select date '2021-02-15' from dual union all
Select date '2021-05-31' from dual union all
Select date '2021-07-04' from dual union all
Select date '2021-09-06' from dual union all
Select date '2021-10-11' from dual union all
Select date '2021-11-11' from dual union all
Select date '2021-11-25' from dual union all
Select date '2021-12-25' from dual
) --select * from holiday_calendar;
, month_business_calendar (dom, bus_day_num) as
( select dom, row_number() over(order by dom)
from (select trunc(date_in ,'mon') + (level-1) dom
from dual connect by level <= extract(day from last_day(date_in))
)
where to_char(dom,'dy', 'NLS_DATE_LANGUAGE=AMERICAN') not in ('sat','sun')
and trunc(dom) not in (select h_date from holiday_calendar)
)
select monthly_business_day_calendar_typ(dom, bus_day_num)
bulk collect
into monthly_business_day_calendar
from month_business_calendar;
return monthly_business_day_calendar;
end make_monthly_business_calendar;
/
select day_of_month, business_day_num
from table(make_monthly_business_calendar(sysdate)) ;
| DAY_OF_MONTH | BUSINESS_DAY_NUM |
|---|---|
| 2021-09-01 | 1 |
| 2021-09-02 | 2 |
| 2021-09-03 | 3 |
| 2021-09-07 | 4 |
| 2021-09-08 | 5 |
| 2021-09-09 | 6 |
| 2021-09-10 | 7 |
| 2021-09-13 | 8 |
| 2021-09-14 | 9 |
| 2021-09-15 | 10 |
| 2021-09-16 | 11 |
| 2021-09-17 | 12 |
| 2021-09-20 | 13 |
| 2021-09-21 | 14 |
| 2021-09-22 | 15 |
| 2021-09-23 | 16 |
| 2021-09-24 | 17 |
| 2021-09-27 | 18 |
| 2021-09-28 | 19 |
| 2021-09-29 | 20 |
| 2021-09-30 | 21 |
select day_of_month
from table(make_monthly_business_calendar(date '2021-09-08'))
where business_day_num = 5
union all
select day_of_month
from table(make_monthly_business_calendar(date '2021-01-21'))
where business_day_num = 5
union all
select day_of_month
from table(make_monthly_business_calendar(date '2021-02-17'))
where business_day_num = 5
union all
select day_of_month
from table(make_monthly_business_calendar(date '2021-09-08'))
where business_day_num = 12 ;
| DAY_OF_MONTH |
|---|
| 2021-09-08 |
| 2021-01-08 |
| 2021-02-05 |
| 2021-09-17 |