Calculating Work Hours

Calculating work hours is a very frequent need in scheduling, billing, payroll, and time and attendance applications. Normally this is best done using an auxiliary Calendar table which provides easy way for calculation and flexibility to account for holidays and other events. However, sometimes the requirements are simplified and the point of interest is only the work hours between two dates, accounting only for weekends, without holidays. Here is one way to perform this calculation using the date and time functions in SQL Server. Note that for this example the week start day is logically set to Monday by adding (@@DATEFIRST - 1) to the start date.

CREATE FUNCTION dbo.CalcWorkHours (
 @start_date DATETIME,
 @end_date DATETIME) 
RETURNS INT
AS
BEGIN
  RETURN
  (SELECT ((total_days / 7) * 5 + total_days % 7 - 
           CASE WHEN 6 BETWEEN start_weekday AND end_weekday
                THEN 1 ELSE 0 END -
           CASE WHEN 7 BETWEEN start_weekday AND end_weekday
                THEN 1 ELSE 0 END) * 8
    FROM (SELECT total_days, start_weekday,
                 start_weekday + total_days % 7 - 1
          FROM (SELECT DATEDIFF(day, @start_date, @end_date) + 1,
                       DATEPART(WEEKDAY, @start_date + @@DATEFIRST - 1)
               ) AS T(total_days, start_weekday)
         ) AS D(total_days, start_weekday, end_weekday)
  );
END
GO
 
SELECT dbo.CalcWorkHours('20080401', '20080421') 
 
/*
 
-- Result
120
 
*/