Find working days between two dates in SQL Server (2023)

Skip to content

Find working days between two dates in SQL Server (1)


SQL Server Education (by the geeks, for the geeks)

Main Menu

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.

Find working days between two dates in SQL Server (2)

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Related Posts

SQL Server Memory Troubleshooting

Performance Monitor Counters

5 Comments on “Find working days between two dates in SQL Server”

  1. 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
    if Datename(dw,@startdate)in('Monday','Tuesday','Wednesday','Thursday','Friday')
    set @a=@a+1
    set @startdate=DATEADD(d,1,@startdate)
    select @a


  2. 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. Thanks


  3. 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


Leave a Reply

Top Articles
Latest Posts
Article information

Author: Fr. Dewey Fisher

Last Updated: 10/12/2023

Views: 6203

Rating: 4.1 / 5 (42 voted)

Reviews: 89% of readers found this page helpful

Author information

Name: Fr. Dewey Fisher

Birthday: 1993-03-26

Address: 917 Hyun Views, Rogahnmouth, KY 91013-8827

Phone: +5938540192553

Job: Administration Developer

Hobby: Embroidery, Horseback riding, Juggling, Urban exploration, Skiing, Cycling, Handball

Introduction: My name is Fr. Dewey Fisher, I am a powerful, open, faithful, combative, spotless, faithful, fair person who loves writing and wants to share my knowledge and understanding with you.