Counting business days between 2 dates

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! Smile

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 Smile

3 comments

  1. … 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

  2. 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

  3. 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.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.