Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Calculate Working Days Between 2 Values In SQL

DZone's Guide to

Calculate Working Days Between 2 Values In SQL

·
Free Resource
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 
Topics:

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}