Calculate Working Days Between 2 Values In SQL
Join the DZone community and get the full member experience.Join For Free
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 EndTo run:
select dbo.fCalcNumBusDays('7/1/2008','7/31/2008') as NumBusDays
Opinions expressed by DZone contributors are their own.