Skip to content
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.
About SQL Geek
5 Comments on “Find working days between two dates in SQL Server”
declare @startdate date,
@enddate date, @a integer
I am struggling with the following problem. I need to calculate hours between 2 dates. For example let say that the start day is 01.03.2018 -Thursday and the end day is 06.03.2018 – Tuesday. I want to be able to calculate only the working hours between these 2 days. Let say that working hours are between 9 and 18. I tried a few things but no luck. Any help will be much appreciated. Thanks
thnk you for this function i really lookiing for it but a find it at the end thnks , a hane a small qu’estion what about if a want to add the Holidays also to the weekends