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

 

*/

17 replies
  1. roji
    roji says:

    what should i change/add to this function to make it work exactly like this "select dbo.CalcBusinessHours('2011-06-03 11:00','2011-06-03 13:00') "

    Reply
  2. Plamen Ratchev
    Plamen Ratchev says:

    Roji,

    First you have to replace "* 8" with "* 4" because your work is 4 hours. Then remove the "CASE WHEN 7 THEN …" from the calculation because you do not treat Sunday as non-work day. That should do it.

    Reply
  3. roji
    roji says:

    Mr.Plame,

    Thank you so much…I am still learning sql and trying to understand how to implement such a function.i have spent lotz of time searching about dateoff,datediff ,dateadd etc.but honesly i didn't find anything as good as urs.Anyways here what i am planning to do :

    1- my business hours r from (9am to 6pm) , and -1 hour lunch break.

    2- days of business : monday to saturday ,however saturday is a half day business , from 9am to 1pm.

    3- function should be "select dbo.CalcBusinessHours('2011-06-03 9:00','2011-06-03 18:00') "

    4- sunday is holiday.

    Please try to help me with this.Thanks yours roji zn

    Reply
  4. Plamen Ratchev
    Plamen Ratchev says:

    Roji,

    To solve this problem the best is to create a calendar table. See the following link for more details: http://pratchev.blogspot.com/2007/12/auxiliary-tables.html.

    Here is one example with your requirements. In essence you populate the calendar table with sufficient number of days (10 years is a good start). Then assign the work hours for each day. Calculating the business hours between two dates becomes very easy because you just need to sum that work hours for all days in the range. The last query in my example below does that. You can create a function out of it but it is not necessary, you can just use the query as it is very simple.

    IF OBJECT_ID(N'Numbers', N'U') IS NOT NULL
    DROP TABLE Numbers;

    CREATE TABLE Numbers (
    nbr INT NOT NULL
    CONSTRAINT df_numbers_nbr
    DEFAULT 1
    CONSTRAINT ck_numbers_nbr
    CHECK (nbr > 0)
    CONSTRAINT pk_numbers
    PRIMARY KEY);

    WITH
    Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
    Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
    Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
    Nums (n) AS
    (SELECT ROW_NUMBER() OVER(ORDER BY n)
    FROM Num5)
    INSERT INTO Numbers (nbr)
    SELECT n FROM Nums
    WHERE n <= 10000;

    IF OBJECT_ID(N'Calendar', N'U') IS NOT NULL
    DROP TABLE Calendar;

    CREATE TABLE Calendar (
    cdate DATETIME NOT NULL
    CONSTRAINT df_calendar_cdate
    DEFAULT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
    CONSTRAINT ck_calendar_cdate
    CHECK (cdate = DATEADD(day, DATEDIFF(day, 0, cdate), 0))
    CONSTRAINT pk_calendar
    PRIMARY KEY,
    cweekday INT NOT NULL
    CONSTRAINT df_calendar_cweekday
    DEFAULT 1
    CONSTRAINT ck_calendar_cweekday
    CHECK (cweekday BETWEEN 1 AND 7),
    cday_name VARCHAR(9) NOT NULL
    CONSTRAINT df_calendar_cday_name
    DEFAULT 'N/A',
    work_hours INT NOT NULL
    CONSTRAINT df_calendar_work_hours
    DEFAULT 0);

    — insert 10 years starting from Jan 1 2008
    INSERT INTO Calendar (cdate)
    SELECT DATEADD(dd, N.nbr – 1, '20080101')
    FROM Numbers AS N
    WHERE N.nbr <= 3653;

    — set days of the week
    UPDATE Calendar
    SET cweekday = DATEPART(dw, cdate + @@DATEFIRST – 1),
    cday_name = DATENAME(dw, cdate);

    — set hours
    UPDATE Calendar
    SET work_hours = CASE cday_name
    WHEN 'Saturday' THEN 4 — 9am to 1pm
    WHEN 'Sunday' THEN 0 — not a work day
    ELSE 8 — 9am to 6pm with 1 hour break
    END;

    — calculate business hours between 2011-06-01 and 2011-06-05
    SELECT MIN(cdate) AS range_start,
    MAX(cdate) AS range_end,
    SUM(work_hours) AS business_hours
    FROM Calendar
    WHERE cdate BETWEEN '20110601' AND '20110605';

    /

    range_start range_end business_hours
    ———————– ———————– ————–
    2011-06-01 00:00:00.000 2011-06-05 00:00:00.000 28

    /

    Reply
  5. Izhar Ab Rani
    Izhar Ab Rani says:

    Declare
    @StartDate Datetime,
    @EndDate Datetime,
    @FirstDaySecDiff int,
    @LastDaySecDiff int,
    @CummulativeSecs int = 0,
    @TotalSecs int = 0,
    @WorkDaySecs int = 43200, –> 60 secs X 60 Minutes X 12 hours (8am to 8 pm)
    @SaturdaySecs int = 14400, –> 60 secs X 60 Minutes X 4 hours (8am to 12 noon)
    @LoopInt int,
    @StartVariable int = 0,
    @VariableDate datetime

    set @StartDate = '9/19/2012 10:35:45'
    set @EndDate = '9/19/2012 4:33 PM'
    set @VariableDate = @StartDate

    select @FirstDaySecDiff = DATEDIFF(ss,@StartDate, CONVERT(datetime,convert(varchar(8),@StartDate,112) + ' 20:00:00'))
    select @LastDaySecDiff = DATEDIFF(ss,CONVERT(datetime,convert(varchar(8),@EndDate,112) + ' 08:00:00'),@EndDate)
    –select @FirstDaySecDiff/3600.00
    select @LoopInt = DATEDIFF(dd ,@StartDate,@EndDate) – 1
    –select 60 * 4 * 60

    if @LoopInt < 0
    begin
    select @LoopInt = 0
    select @LastDaySecDiff = 0 — (Same Day)
    end

    –select @StartVariable, @LoopInt
    While @StartVariable < @LoopInt and @LoopInt <> 0
    begin

    if DateName(weekday,@VariableDate) = 'Saturday'
    begin
    select @CummulativeSecs = @CummulativeSecs + @SaturdaySecs
    end

    if DateName(weekday,@VariableDate) not in ('Saturday','Sunday')
    begin
    select @CummulativeSecs = @CummulativeSecs + @WorkDaySecs
    end

    select @StartVariable = @StartVariable + 1
    select @VariableDate = @StartDate + 1
    Continue
    end

    select @TotalSecs = isnull(@CummulativeSecs,0) + isnull(@FirstDaySecDiff,0) + isnull(@LastDaySecDiff,0)

    DECLARE @retval VARCHAR(30);
    SET @retval = cast(@TotalSecs/(6060) as varchar(10))+':'+
    cast( (@TotalSecs-@TotalSecs/(60
    60)3600)/60 as varchar(10))+':'+
    cast( (@TotalSecs-@TotalSecs/(60)
    60) as varchar(10));

    select @TotalSecs as Seconds,dbo.FNC_RETURN_HOUR_MINUTE_SECONDS(@TotalSecs) as 'Working Days',@retval

    Reply
    • Plamen Ratchev
      Plamen Ratchev says:

      The function takes as parameters only the start and the end dates, no hours. It is hard coded to calculate 8 hours each day. You would have to modify it to calculate specific hours.

      Reply
  6. Petya Ivanova
    Petya Ivanova says:

    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

    Reply
  7. Plamen Ratchev
    Plamen Ratchev says:

    Hi Petya,

    In your case you can use the function above as is. It is hard coded to calculate 8 work hours per day between the two dates, excluding weekends. For your example you can try it like this:

    SELECT dbo.CalcWorkHours(‘20180301’, ‘20180306’) ;

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *