DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Stephen has posted 20 posts at DZone. View Full User Profile

Calculate Working Days Between 2 Values In SQL

10.13.2008
| 10912 views |
  • submit to reddit
        Here we are declaring two variables to use within the code. @NUMBUSDAYS will start at 0 and then we will increment accordingly. @NUMTOTALDAYS will start as the difference between the start and end dates. The two date variables are used for the begin and end dates. In this example, they have been hardcoded.

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON

go

CREATE function [dbo].[fCalcNumBusDays] (@StartDate datetime, @EndDate datetime)

returns int

as
begin
    DECLARE @NUMBUSDAYS AS INT
    DECLARE @NUMTOTALDAYS AS INT

    SET @NUMBUSDAYS=0
    SET @NUMTOTALDAYS=DATEDIFF(DD,@STARTDATE,@ENDDATE)

    WHILE @NUMTOTALDAYS>=0

    BEGIN
        IF (DATEPART(DW,DATEADD(D,@NUMTOTALDAYS,@STARTDATE)) >1 AND DATEPART(DW,DATEADD(D,@NUMTOTALDAYS,@STARTDATE))<7)
            BEGIN 
                SET @NUMBUSDAYS=@NUMBUSDAYS+1
            END

        SET @NUMTOTALDAYS=@NUMTOTALDAYS-1
    END

    RETURN @NUMBUSDAYS

End 

To run:
select dbo.fCalcNumBusDays('7/1/2008','7/31/2008') as NumBusDays