Many a times it is required to find working days between two dates. We do know that DATEDIFF function can be used to find differences between two dates; however the working days calculation excludes weekends and holidays. This solution doesn’t consider holidays. The function for the same is given below.
CREATE FUNCTION [dbo].fn_CountWeekDays ( @fromdate Datetime, @todate Datetime ) RETURNS TABLE AS RETURN ( SELECT (DATEDIFF(dd, @fromdate, @todate) + 1) -(DATEDIFF(wk, @fromdate, @todate) * 2) -(CASE WHEN DATENAME(dw, @fromdate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, @todate) = 'Saturday' THEN 1 ELSE 0 END) As NoOfWeekDays )
The above function is an inline table valued function which accepts two parameters @fromdate and @todate. The statement DATEDIFF(dd,@fromdate,@todate) + 1 gives the number of dates between the two dates. The statement DATEDIFF(wk,@fromdate,@todate) gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count. The next two statements excludes the day if it’s a Saturday or Sunday.
The output from the function is given below.