Artful Common Queries

Count business days between two dates

from the Artful Common Queries page

The simplest way to count business days between any two dates is to use a calendar table calendar(d date,isholiday bool) populated for all days in all possibly relevant years. Then the following query gives the inclusive number of business days between dates dStart and dStop:

SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN dStart AND dStop 
  AND DAYOFWEEK(d) NOT IN(1,7)
  AND isholiday=0;

If that solution isn't available, you can efficiently walk a little table of ints from 0 through 9 with enough cross joins to cover the maximum possible day interval (two cross joins cover 1,000 days, three cover 10,000 days, &c;):

-- UTILITY VIEW FOR WALKING INTS
create or replace view ints as
  select 0 as i union all select 1 union all select 2 union all 
  select 3 union all select 4 union all select 5 union all select 6 union all 
  select 7 union all select 8 union all select 9;
-- BIZ DAYS IN MARCH 2019
set @d1='2025-03-01', @d2='2024-03-31';
select count(*) as BizDays
from (
  select AddDate( @d1, interval @i:=@i+1 day ) as day
  from (
    select a.i from ints a cross join ints b cross join ints c
  ) a                                      -- 2 CROSS JOINS COVER 10x10x10 DAYS
  join (select @i := -1) r
  where @i < DateDiff( @d2, @d1 )     
) as dates
where WeekDay(dates.day) between 0 and 4;  -- MONDAY=0
+---------+
| BizDays |
+---------+
|      21 |
+---------+

Last updated 13 Mar 2025