How do I count the number of business days between two dates?

In the very general case, where you only want an absolute count of business days, and don't care about holidays or designated non-work days, you can use simple date logic and arithmetic.

T-SQL

This function was based primarily on work by Itzik Ben-Gan. I hope to investigate a more direct approach using some of the new functionality in SQL Server 2005.

CREATE FUNCTION dbo.GetWorkingDays
(
@startDate SMALLDATETIME,
@endDate SMALLDATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @range INT;

SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;

RETURN
(
SELECT
@range / 7 * 5 + @range % 7 -
(
SELECT COUNT(*)
FROM
(
SELECT 1 AS d
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
) weekdays
WHERE d <= @range % 7
AND DATENAME(WEEKDAY, @endDate - d + 1)
IN
(
'Saturday',
'Sunday'
)
)
);
END
GO

-- normal case... both working days, span a weekend
PRINT dbo.getWorkingDays('20051109', '20051114')

-- other... both days in same week
PRINT dbo.getWorkingDays('20051102', '20051104')

-- other... both days = same day
PRINT dbo.getWorkingDays('20051104', '20051104')

-- both days = same day, weekend
PRINT dbo.getWorkingDays('20051105', '20051105')

-- whole month
PRINT dbo.getWorkingDays('20051101', '20051130')

-- start sat, end sun
PRINT dbo.getWorkingDays('20051112', '20051127')

In most cases, however, this information isn't going to be specific enough. SQL Server is often expected to not only be able to handle the above in fewer lines of code, but also to factor in holidays and other non-working days. The truth is that, since different regions have different definitions for a week, and that just about every company has a different set of defined working days for a year, that anything more complex than the above would typically warrant the use of a calendar table.

Calendar table

For a much more exhaustive treatment of the advantages and usage of a calendar table, see Article #2519. Here's one approach, using SQL Server (the table design might be slightly different in Access, but the logic will remain the same):

CREATE TABLE dbo.WorkCalendar
(
-- we can use the date as primary key
dt SMALLDATETIME PRIMARY KEY CLUSTERED

,

-- computed column; very deterministic
isWeekDay AS CONVERT(BIT, CASE
WHEN datepart(dw, dt) IN (1,7)
THEN 0 ELSE 1 END)

,

-- not a computed column, because we may
-- need to override
isWorkDay BIT DEFAULT 1
);
GO

-- populate the table

DECLARE @dt SMALLDATETIME;
SET @dt = '20030101';
WHILE @dt <= '20041231'
BEGIN
INSERT dbo.WorkCalendar(dt) SELECT @dt;
SET @dt = @dt + 1;
END

-- now we have to set the weekends back to 0
UPDATE dbo.WorkCalendar
SET isWorkDay = 0
WHERE isWeekday = 0;

-- now, let's populate some holidays
UPDATE dbo.WorkCalendar
SET isWorkDay = 0
WHERE isWorkDay = 1
AND dt IN
(
'20030101', -- New Years
'20040101',
'20030704', -- July 4
'20040704',
'20031225', -- Christmas
'20041225'
-- ... etc etc ...
);

So now, given two dates within this range, you can calculate the number of work days between them simply by the following:

DECLARE @d1 SMALLDATETIME, @d2 SMALLDATETIME;
SELECT @d1 = '20030601', @d2 = '20030630';

SELECT COUNT(*)
FROM dbo.WorkCalendar
WHERE dt >= @d1
AND dt <= @d2
AND isWorkDay = 1;

Results:
--------
21

Now, let's say your company provides a day off on the second Friday in June, we can account for that by modifying the WorkCalendar table:

UPDATE dbo.WorkCalendar
SET isWorkDay = 0
WHERE isWorkDay = 1
AND dt IN
(
'20030613', -- fictional holiday
'20040611',
);

Then, we can run exactly the same query, and we should see one less working day in the date range:

DECLARE @d1 SMALLDATETIME, @d2 SMALLDATETIME;
SELECT @d1 = '20030601', @d2 = '20030630';

SELECT COUNT(*)
FROM dbo.WorkCalendar
WHERE dt >= @d1
AND dt <= @d2
AND isWorkDay = 1;

Results:
--------
20

VBScript

<%
function getWorkingDays(d1, d2)

' assumes inclusive date range
' also assumes holidays are ignored

' check for invalid arguments

if not (isdate(d1) and isdate(d2)) then
response.write "At least one date is invalid."
exit function
else
d1 = cdate(d1): d2 = cdate(d2)
if d1 > d2 then
response.write "Invalid date range."
exit function
end if

end if

' we might need to shave days off beginning
' or end, in case an argument is a weekend

dim subtract, absdays
subtract = 0

select case datepart("w", d1)
case 1
subtract = subtract + 1
case 7
subtract = subtract + 2
end select

select case datepart("w", d2)
case 1
subtract = subtract + 2
case 7
subtract = subtract + 1
end select

' figure out how many actual days
absdays = datediff("d", d1, d2) + 1 - subtract

' subtract the in-between weekends
getWorkingDays = absdays - cint(2 * (absdays \ 7))

' in case both are in the same weekend
if getWorkingDays < 0 then getWorkingDays = 0

end function

' test results!

' normal case... both working days
rw getWorkingDays("2003-06-05", "2003-06-27")

' other... both days in same week
rw getWorkingDays("06/10/2003", "06/12/2003")

' other... both days = same day
rw getWorkingDays("06/10/2003", "06/10/2003")

' other... both days = same day, weekend
' this is what the very last if is for
rw getWorkingDays("06/08/2003", "06/08/2003")

' other... whole month
rw getWorkingDays("06/01/2003", "06/30/2003")

' other... start sat, end sun
rw getWorkingDays("06/07/2003", "06/22/2003")

' just a sub to save typing
sub rw(s)
response.write "<p>" & s
end sub
%>



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

How do I count the number of business days between two dates?

Related Articles