sql server - How to calculate difference between two dates in workdays

Question

I need to calculate difference in workdays between two dates. Is there a built in function for this in SQL Server? Can someone please provide an example on how to do this?

Answer 1

Here is something I wrote quickly. Just encapsulate it into a function or whatever you need.

declare @StartDate datetime
declare @EndDate datetime

declare @TotalDiff int
declare @NumberOfWeekends int

SET @StartDate = '3/12/2013'
SET @EndDate = '3/22/2013'
SET @NumberOfWeekends = 0
SET @TotalDiff = DATEDIFF(d,@StartDate, @EndDate)

If @TotalDiff > 7 
    SET @NumberOfWeekends = @TotalDiff / 7
else if DATEPART(dd, @EndDate) < DATEPART(DD, @StartDate)
    SET @NumberOfWeekends = 1

select (@TotalDiff - 2*@NumberOfWeekends) as TotalWorkDays