none
Get Business Hours Between Dates

    Question

  • We are using SQL 2008R2 and I have the function below in attempt to calculate the number of business hours between 2 dates.  I am also subtracting 30 minutes to allow for employee lunch time.  It does not return the correct # of hours, even though I am using a function that DOES work to return business days between the dates.  An example I am using is @DateFrom = '2013-01-03 15:23:00' and @DateTo = '2013-01-04 11:01:00'.  Thanks.

    ALTER FUNCTION [dbo].[GetBusinessHours]
    (
        @DateFrom smalldatetime,
        @DateTo  smalldatetime
    )
    RETURNS INT
    AS
    BEGIN

     DECLARE @StartTime varchar(8) = '07:30:00';
     DECLARE @EndTime varchar(8) = '16:00:00';
     DECLARE @LunchMinutesFrom int = 0;
     DECLARE @LunchMinutesTo int = 0;
     
     IF DATEPART(hour,@DateFrom) < 12
      BEGIN
       SET @LunchMinutesFrom = 30;
      END

     IF DATEPART(hour,@DateTo) > 12
      BEGIN
       SET @LunchMinutesTo = 30;
      END
     
        RETURN CASE WHEN DATEADD(d,DATEDIFF(d,0,@DateFrom),0) = DATEADD(d,DATEDIFF(d,0,@DateTo),0) THEN (DATEDIFF(minute , @DateFrom , @DateTo) - @LunchMinutesFrom) / 60
           ELSE (DATEDIFF(minute , @DateFrom , CAST(CONVERT(CHAR(10),@DateFrom,23) + ' ' + @EndTime As datetime) - @LunchMinutesFrom) +
          (DATEDIFF(minute , CAST(CONVERT(CHAR(10),@DateTo,23) + ' ' + @StartTime As datetime), @DateTo) - @LunchMinutesTo) +
          (dbo.GetBusinessDays(DATEADD(day,1,DATEADD(d,DATEDIFF(d,0,@DateFrom),0)),DATEADD(day,-1,DATEADD(d,DATEDIFF(d,0,@DateTo),0))) * 480) / 60)
        END;
    END

    Monday, October 21, 2013 10:03 PM

Answers