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

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
      COM / ActiveX Components
      Forms
      General Topics
      Date/Time Routines
      Email Scripts & Info
      Files/Directories & FSO
   Databases
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
classicasp.aspfaq.com

ASP FAQ Tutorials :: Classic ASP 1.0 :: Date/Time Routines :: How do I count the number of business days between two dates?


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 
%>

Related Articles

Can I get millisecond accuracy in ASP?
Can I make VBScript format dates for me?
Could I get a little help with dates?
Given a date, how do I find the beginning and end of that week?
Given two dates, how do I determine an age?
How do I calculate dates, such as the first day of the month?
How do I convert a DATEDIFF to days, hours, and minutes?
How do I convert a timespan, in seconds, to HH:MM:SS?
How do I convert local time to UTC (GMT) time?
How do I delimit/format dates for database entry?
How do I determine the number of seconds since 1/1/1970?
How do I display time in military format?
How do I implement a calendar / datepicker in ASP?
How do I select time only from a DATETIME column?
Why do I have problems inserting NOW() into a database?
Why does JavaScript's document.lastModified() not work in ASP files?

 

 


Created: 6/14/2003 | Last Updated: 11/23/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (369)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...