Posted by One of the most commonly hit questions on AskTom is how to count the number of work days (Mon => Fri) between a start and end date range.
This is not a particular tough problem to solve since we have easy access to the day of the week via TO_CHAR, thus simply cycling between the start and end date looking for weekdays gives us the answer
select count(*)
from ( select rownum rnum
from dual
connect by level <= greatest(:start_date,:end_date) - least(:start_date,:end_date)+1
)
where to_char( least(:start_date,:end_date)+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' );
That solution is fine for the requirement where you have a single pair of inputs. But what if you have a table with thousands or millions of rows, each with a start/end date pair. I don’t want to be cycling through synthesized rows (even if that cycling is just on the DUAL pseudo-table) because that won’t scale, or at best will burn a hole in one of my server CPU cores! 
Rather than cycling through the days between a start and end pair, we can apply some arithmetic rules:
- total number of complete weeks (days difference / 7) between the dates gives multiple of 5 work day,
- but you need to adjust that if the start date falls later in the week than the end date,
- if the start date falls on a saturday/sunday, then adjust a day or two accordingly,
- if the end date falls on saturday/sunday, then adjust a day or two accordingly
After some tinkering, I came up with the following expression:
(I also added greatest/least wrappers so that it didn’t matter if the start date was later than the end date)
(trunc(abs(:start_date-:end_date)/7) +
case when to_number(to_char(least(:start_date,:end_date),'D')) > to_number(to_char(greatest(:start_date,:end_date),'D')) then 1 else 0 end
) * 5 +
to_number(to_char(greatest(:start_date,:end_date),'D')) - to_number(to_char(least(:start_date,:end_date),'D')) +
case when to_number(to_char(least(:start_date,:end_date),'D')) in (1,7) then 0 else 1 end +
case when to_number(to_char(least(:start_date,:end_date),'D')) = 7 then 1 else 0 end +
case when to_number(to_char(greatest(:start_date,:end_date),'D')) = 7 then -1 else 0 end
Now that this an expression, it can trivially be added as a virtual column to an existing table if needed.
SQL> create table t ( sd date, ed date);
Table created.
SQL>
SQL> insert into t
2 select date '2020-01-01'-20+rownum*2, date '2020-01-01'-10+rownum
3 from dual
4 connect by level <= 30;
30 rows created.
SQL>
SQL> select * from t;
SD ED
--------- ---------
14-DEC-19 23-DEC-19
16-DEC-19 24-DEC-19
18-DEC-19 25-DEC-19
20-DEC-19 26-DEC-19
22-DEC-19 27-DEC-19
24-DEC-19 28-DEC-19
26-DEC-19 29-DEC-19
28-DEC-19 30-DEC-19
30-DEC-19 31-DEC-19
01-JAN-20 01-JAN-20
03-JAN-20 02-JAN-20
05-JAN-20 03-JAN-20
07-JAN-20 04-JAN-20
09-JAN-20 05-JAN-20
11-JAN-20 06-JAN-20
13-JAN-20 07-JAN-20
15-JAN-20 08-JAN-20
17-JAN-20 09-JAN-20
19-JAN-20 10-JAN-20
21-JAN-20 11-JAN-20
23-JAN-20 12-JAN-20
25-JAN-20 13-JAN-20
27-JAN-20 14-JAN-20
29-JAN-20 15-JAN-20
31-JAN-20 16-JAN-20
02-FEB-20 17-JAN-20
04-FEB-20 18-JAN-20
06-FEB-20 19-JAN-20
08-FEB-20 20-JAN-20
10-FEB-20 21-JAN-20
30 rows selected.
SQL>
SQL> alter table t add business_days number generated always as (
2 (trunc(abs(sd-ed)/7) +
3 case when to_number(to_char(least(sd,ed),'D')) > to_number(to_char(greatest(sd,ed),'D')) then 1 else 0 end
4 ) * 5 +
5 to_number(to_char(greatest(sd,ed),'D')) - to_number(to_char(least(sd,ed),'D')) +
6 case when to_number(to_char(least(sd,ed),'D')) in (1,7) then 0 else 1 end +
7 case when to_number(to_char(least(sd,ed),'D')) = 7 then 1 else 0 end +
8 case when to_number(to_char(greatest(sd,ed),'D')) = 7 then -1 else 0 end
9 );
Table altered.
SQL>
SQL> select * from t;
SD ED BUSINESS_DAYS
--------- --------- -------------
14-DEC-19 23-DEC-19 6
16-DEC-19 24-DEC-19 7
18-DEC-19 25-DEC-19 6
20-DEC-19 26-DEC-19 5
22-DEC-19 27-DEC-19 5
24-DEC-19 28-DEC-19 4
26-DEC-19 29-DEC-19 2
28-DEC-19 30-DEC-19 1
30-DEC-19 31-DEC-19 2
01-JAN-20 01-JAN-20 1
03-JAN-20 02-JAN-20 2
05-JAN-20 03-JAN-20 1
07-JAN-20 04-JAN-20 2
09-JAN-20 05-JAN-20 4
11-JAN-20 06-JAN-20 5
13-JAN-20 07-JAN-20 5
15-JAN-20 08-JAN-20 6
17-JAN-20 09-JAN-20 7
19-JAN-20 10-JAN-20 6
21-JAN-20 11-JAN-20 7
23-JAN-20 12-JAN-20 9
25-JAN-20 13-JAN-20 10
27-JAN-20 14-JAN-20 10
29-JAN-20 15-JAN-20 11
31-JAN-20 16-JAN-20 12
02-FEB-20 17-JAN-20 11
04-FEB-20 18-JAN-20 12
06-FEB-20 19-JAN-20 14
08-FEB-20 20-JAN-20 15
10-FEB-20 21-JAN-20 15
30 rows selected.
Note: Day of Week calculations from TO_CHAR can vary due to NLS settings, so you might need to alter this expression slightly if you don’t live in Australia
Learning is not a spectator sport 
… you might need to alter this expression slightly if you don’t live in Australia …
not quite true.
you do not have to alter it if you live in
ith function day_of_week_in_terr(d in date, terr in varchar2) return number is
begin
execute immediate 'alter session set nls_territory='''||terr||'''';
return to_number(to_char(d, 'D'));
end day_of_week_in_terr;
select
listagg(territory, ' or ') within group(order by territory) as you_have_to_live_in
from
(
select
nvv.value territory, day_of_week_in_terr(sysdate, nvv.value) day_of_week_in_terr
from
v$nls_valid_values nvv
where
nvv.PARAMETER='TERRITORY'
)
group by
day_of_week_in_terr
having
listagg(territory, ' or ') within group(order by territory) like '%AUSTRALIA%'
/
ALBANIA or ARGENTINA or ARMENIA or AUSTRALIA or AUSTRIA or AZERBAIJAN or BELARUS or BELGIUM or BERMUDA or BOLIVIA or BOSNIA AND HERZEGOVINA or BULGARIA or CAMEROON or CATALONIA or CHILE or CIS or CONGO BRAZZAVILLE or CONGO KINSHASA or CROATIA or CYPRUS or CZECH REPUBLIC or CZECHOSLOVAKIA or DENMARK or ECUADOR or ESTONIA or ETHIOPIA or FINLAND or FRANCE or FYR MACEDONIA or GABON or GERMANY or GREECE or HONDURAS or HUNGARY or ICELAND or INDONESIA or IRELAND or ITALY or IVORY COAST or KAZAKHSTAN or LATVIA or LITHUANIA or LUXEMBOURG or MACEDONIA or MEXICO or MONTENEGRO or NEW ZEALAND or NICARAGUA or NORWAY or PARAGUAY or POLAND or ROMANIA or RUSSIA or SENEGAL or SERBIA or SERBIA AND MONTENEGRO or SLOVAKIA or SLOVENIA or SPAIN or SRI LANKA or SWEDEN or SWITZERLAND or THE NETHERLANDS or TURKEY or UGANDA or UKRAINE or UNITED KINGDOM or URUGUAY or UZBEKISTAN or YUGOSLAVIA or ZAMBIA
Hi Connor,
Just for fun, here is another variant:
alter table t add business_days number generated always as (
(TRUNC(greatest(sd,ed),’IW’) – TRUNC(least(sd,ed),’IW’)) / 7 * 5
+ DECODE(TO_CHAR(greatest(sd,ed),’D’),’1′,5,’2′,1,’3′,2,’4′,3,’5′,4,’6′,5,’7′,5)
– DECODE(TO_CHAR(least(sd,ed),’D’), ‘1’,5,’2′,0,’3′,1,’4′,2,’5′,3,’6′,4,’7′,5)
)
/
A little verbose, but kind of “symmetric” in the sd/ed usage.
Cheers & Best Regards,
Iudith Mentzel
Hi Connor,
interesting Oracle lets you create a virtual column here though to_char(…, ‘D’) is not deterministic (depends on NLS_TERRITORY).
An improvement which does not depend on NLS-settings might be
alter table t add business_days number generated always as (
(trunc(abs(sd-ed)/7) +
case when mod(to_number(to_char(least(sd,ed),’J’)), 7) > mod(to_number(to_char(greatest(sd,ed),’J’)), 7) then 1 else 0 end
) * 5 +
to_number(to_char(greatest(sd,ed),’D’)) – 1 – mod(to_number(to_char(least(sd,ed),’J’)), 7) +
case when mod(to_number(to_char(least(sd,ed),’J’)), 7) in (0,6) then 0 else 1 end +
case when mod(to_number(to_char(least(sd,ed),’J’)), 7) = 6 then 1 else 0 end +
case when mod(to_number(to_char(greatest(sd,ed),’J’)), 7) = 6 then -1 else 0 end
);
Furthermore, your solution does not work (unexpectedly) only for people living in Australia, but for all people living in
with function day_of_week_in_terr(d in date, terr in varchar2) return number is
begin
execute immediate ‘alter session set nls_territory=”’||terr||””;
return to_number(to_char(d, ‘D’));
end day_of_week_in_terr;
select
listagg(territory, ‘ or ‘) within group(order by territory) as you_have_to_live_in
from
(
select
nvv.value territory, day_of_week_in_terr(sysdate, nvv.value) day_of_week_in_terr
from
v$nls_valid_values nvv
where
nvv.PARAMETER=’TERRITORY’
)
group by
day_of_week_in_terr
having
listagg(territory, ‘ or ‘) within group(order by territory) like ‘%AUSTRALIA%’
/
ALBANIA or ARGENTINA or ARMENIA or AUSTRALIA or AUSTRIA or AZERBAIJAN or BELARUS or BELGIUM or BERMUDA or BOLIVIA or BOSNIA AND HERZEGOVINA or BULGARIA or CAMEROON or CATALONIA or CHILE or CIS or CONGO BRAZZAVILLE or CONGO KINSHASA or CROATIA or CYPRUS or CZECH REPUBLIC or CZECHOSLOVAKIA or DENMARK or ECUADOR or ESTONIA or ETHIOPIA or FINLAND or FRANCE or FYR MACEDONIA or GABON or GERMANY or GREECE or HONDURAS or HUNGARY or ICELAND or INDONESIA or IRELAND or ITALY or IVORY COAST or KAZAKHSTAN or LATVIA or LITHUANIA or LUXEMBOURG or MACEDONIA or MEXICO or MONTENEGRO or NEW ZEALAND or NICARAGUA or NORWAY or PARAGUAY or POLAND or ROMANIA or RUSSIA or SENEGAL or SERBIA or SERBIA AND MONTENEGRO or SLOVAKIA or SLOVENIA or SPAIN or SRI LANKA or SWEDEN or SWITZERLAND or THE NETHERLANDS or TURKEY or UGANDA or UKRAINE or UNITED KINGDOM or URUGUAY or UZBEKISTAN or YUGOSLAVIA or ZAMBIA.