title: calculate hours excluding weekends, holidays and non-working hours with SSMS
body: <p>First of all I wanna say I am new to SSMS, so please bear with me if some things are not totally clear. So here is my issue.</p>
  
  <p>I am trying to calculate time of 2 dates within the working hours and it should exclude the weekends and holidays. </p>
  
  <p>I found a function script which calculated the time within the working hours and it did exclude the weekends. Now I just miss the part to exclude holidays. I have made a Holiday table which contains the name and date of the holidays. </p>
  
  <p>I had found another function which calculates the time without the holidays, but I am getting way too much of output when I run a query with it. And I am not sure what is going wrong.</p>
  
  <p>This is the new function I am using which is giving way too much output. </p>
  
  <pre><code>CREATE FUNCTION getWorkingHoursDiff(
  @startDate datetime,
  @endDate datetime)
  RETURNS int AS
  BEGIN
  
      DECLARE @totaldays INT; 
      DECLARE @weekenddays INT;    
  
      DECLARE @ShiftStartOn datetime, @ShiftEndOn datetime
      Declare @startHourDiff int, @endHourDiff int
  
      set @ShiftStartOn = cast(CONVERT(VARCHAR,@startDate,110)+' 08:00' as Datetime)
      set @ShiftEndOn = cast(CONVERT(VARCHAR, @endDate,110)+' 18:00' as Datetime)
  
      declare @ShiftHours int
      set @ShiftHours = DATEDIFF(HOUR, @ShiftStartOn, @ShiftEndOn)
  
      set @startHourDiff = 0
  
      if @startDate between @ShiftStartOn and DATEADD(hour, @ShiftHours, @ShiftStartOn)
      begin
          set @startHourDiff = DATEDIFF(HOUR, @ShiftStartOn, @startDate)    
          set @startDate = @ShiftStartOn
  
          if(@startHourDiff &lt; 0)    
          begin        
              set @startHourDiff = 0    
          end        
      end
      if DATEPART(WEEKDAY, @startDate) = 1 or DATEPART(WEEKDAY, @startDate) = 7
          set @startHourDiff = 0
  
      if exists(select 1 from table_holiday where DATEDIFF(day, start_time, @startDate) = 0)
          set @startHourDiff = 0    
  
      if @startDate &gt; DATEADD(hour, @ShiftHours, @ShiftStartOn)
          set @startDate = DATEADD(day,1,@ShiftStartOn)    
  
      set @endHourDiff = 0    
      if @endDate between DATEADD(hour, -@ShiftHours, @ShiftEndOn) and @ShiftEndOn
      begin
          set @endHourDiff = DATEDIFF(HOUR, @endDate, @ShiftEndOn)            
  
          set @endDate = @ShiftEndOn
  
          if(@endHourDiff &lt; 0)
              set @endHourDiff = 0    
      end
      if DATEPART(WEEKDAY, @endDate) = 1 or DATEPART(WEEKDAY, @endDate) = 7
          set @endHourDiff = 0
  
      if exists(select 1 from table_holiday where DATEDIFF(day, start_time, @endDate) = 0)
          set @endHourDiff = 0
  
      if @endDate &lt; DATEADD(hour, -@ShiftHours, @ShiftEndOn)
          set @endDate = DATEADD(day,-1,@ShiftEndOn)
  
      SET @totaldays = DATEDIFF(DAY, @startDate, @endDate) +1     
  
      SET @weekenddays = ((DATEDIFF(WEEK, @startDate, @endDate) * 2) + -- get the number of weekend days in between
                         CASE WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN 1 ELSE 0 END + -- if selection was Sunday, won't add to weekends
                         CASE WHEN DATEPART(WEEKDAY, @endDate) = 7 THEN 1 ELSE 0 END)  -- if selection was Saturday, won't add to weekends
  
      select @totaldays = @totaldays - @weekenddays
  
      declare @chkdtFromDate datetime, @chkdtToDate datetime
  
      select @chkdtFromDate =  DATEADD(dd, DATEDIFF(dd, 0, @startDate), 0)
      select @chkdtToDate =  DATEADD(dd, DATEDIFF(dd, 0, @endDate), 0)
  
      select @totaldays = @totaldays - COUNT(1) from table_holiday where (start_time between @chkdtFromDate and @chkdtToDate)
  
      return (@totaldays*@ShiftHours) - @startHourDiff - @endHourDiff
  End
  Go
  </code></pre>
  
  <p>This shows you the output with the both the functions I have added. The first function works perfectly fine as you see in the result. But it does not exclude holidays.</p>
  
  <p>The second result you see gives even more hours as shown in column <code>OpenstaandeTijdInUren</code> while it should provide me a value of 10 hours since 05-30 is a holiday.</p>
  
  <p><a href="https://i.stack.imgur.com/U305y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/U305y.png" alt="enter image description here"></a></p>