Skip to content
SQL Server Education (by the geeks, for the geeks)
Main Menu
- by SQL Geek - 5 Comments.
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.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Related Posts
About SQL Geek
5 Comments on “Find working days between two dates in SQL Server”
declare @startdate date,
@enddate date, @a integer
set @startdate=’2016-04-01′
set @enddate=’2016-04-30′
set @a=0
select @startdate
select @enddate
while @startdate<=@enddate
Begin
if Datename(dw,@startdate)in('Monday','Tuesday','Wednesday','Thursday','Friday')
begin
set @a=@a+1
end
set @startdate=DATEADD(d,1,@startdate)
end;
select @aReply
Hello everyone,
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. ThanksReply
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
Reply