Excel NetworkDays Function in SQL

I was working on a report last week and we had the need to look at 2 dates and calculate the time between them, but not include weekends. It’s interesting that Excel has this functionality called Networkdays which does exactly this. SQL doesn’t natively.

I scratched around and looked at a few options, and found that the best way to resolve this is to create your own function in SQL, then call it.

To create the function use the following SQL:

USE [YOURDATABASENAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[hours_diff] ( @date1 datetime,@date2 datetime ) 
RETURNS int
AS
BEGIN

declare @i int
Declare @count int
declare @diff int
set @diff=datediff(hh,@date1,@date2)
set @i=0
set @count=0
While(@i<@diff)
Begin
Select @count=@count+1 where datename(dw,dateadd(d,@i,@date1))
not in('Saturday','Sunday')
set @i=@i+1
End

RETURN @count
END
GO

Now in the example above I actually wanted to run this function against 2 dates and pull out the hours minus the weekend days so to get that the line:

set @diff=datediff(hh,@date1,@date2)

uses the hh abbreviation for hour, however if you just wanted to see days you could use:

set @diff=datediff(dd,@date1,@date2)

Once this function is set you could then use something like:

Select

Dbo.hours_diff(Date1,Date2) as hours

This would run the function just created against Date1 and Date2 and give you the results in hours –weekend days (Saturday, Sunday)

Enjoy