Navigation

Quick Search
»
Advanced Search »

Contributor Links

 Create a new Page Administration File Management Login/Logout Your Profile

NetWorkDays Function - SQL Server

Modified on Wed, Oct 22, 2008, 8:21 PM Categorized as Microsoft Office, SQL Server
This function reproduces the Excel function NETWORKDAYS().

```/*--------------------------------------------------------------------------------
Credits:    Patrick Jasinski, 02/27/08
Purpose:    This function reproduces the Excel function NETWORKDAYS(), which is
available via the Excel Analysis Add-In.
Algorithm
(1) Calculate the number of days between the First Sunday AFTER StartDate and
the First Sunday BEFORE EndDate.  At 7 days per week, convert this to
weeks; then, at 5 weekdays per week, convert this to weekdays.
(2) Add in the weekdays between the Start Date and the following Sunday.
(3) Add in the weekdays between the EndDate and the previous Sunday.
--------------------------------------------------------------------------------*/
create function [dbo].[NetWorkDays](
@StartDate datetime
,@EndDate datetime
) returns int as begin

declare
@result int
,@StartDate2 datetime
,@EndDate2 datetime

set @StartDate2 = dateadd(d,8-datepart(dw, @StartDate), @StartDate)
set @EndDate2   = dateadd(d,1-datepart(dw ,@EndDate), @EndDate)

set @result = datediff(d, @StartDate2, @EndDate2) * 5 / 7
+ datediff(d, @StartDate, @StartDate2) - 1
+ datediff(d, @EndDate2, @EndDate)
- case when datepart(dw,@StartDate) = 1 then 1 else 0 end
- case when datepart(dw,@EndDate) = 7 then 1 else 0 end

return @result

end```

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2020, Patrick Jasinski.