Calculating business hours

This is a function modified from my earlier blog
The below function basically calculates the total business hours elapsed between two date periods taking into consideration business start and end times. It also assumes that there’s an existing table Holiday that stores holiday information and excludes them also in calculation of business hour.Just in case you don’t have holiday table or don’t need to consider holiday information, you can remove code in red below to consider only weekend offs.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME =’BusinessHours’ AND ROUTINE_SCHEMA = ‘dbo’ AND ROUTINE_TYPE=’FUNCTION’)
DROP FUNCTION dbo.BusinessHours
GO
CREATE FUNCTION dbo.BusinessHours
(
@StartDate datetime, –start of period of consideration for calculation of business hours
@EndDate datetime, –end of period of consideration for calculation of business hours
@BusinessStart datetime, –start of business hours
@BusinessEnd datetime –end of business hours
)
RETURNS int
AS
BEGIN
DECLARE @TotalHours int
IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
BEGIN
 IF @BusinessStart>@BusinessEnd
 BEGIN
  SET @TotalHours= -1
 END
 ELSE
BEGIN
SELECT @BusinessStart=CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) ELSE @BusinessStart END,
@BusinessEnd =CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessEnd
THEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) ELSE @BusinessEnd END
  SET @TotalHours= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
      THEN 0
ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)/60
END
END
END
ELSE
BEGIN
 SELECT @StartDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),1))
 WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))
   ELSE @StartDate
   END,
  @EndDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),-1))
 WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),0))
   ELSE @EndDate
   END
  ;With Calendar_CTE (Date,Day,WeekDay)
 AS
 (
 SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))%7 < 5 THEN 1 ELSE 0 END
  UNION ALL
  SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,1,Date))%7 < 5 THEN 1 ELSE 0 END
 FROM Calendar_CTE
 WHERE DATEADD(dd,1,Date) <= @EndDate
 )

  SELECT @TotalHours=CEILING(SUM(
   CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
   WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
   ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
   END
   )/60.0)
 FROM Calendar_CTE c
 LEFT JOIN Holiday h
 ON h.[date]= c.Date
 WHERE WeekDay=1
 AND h.[date] IS NULL
 OPTION (MAXRECURSION 0)
END
RETURN @TotalHours
END

As an example see
SELECT dbo.BusinessHours(‘2010-01-01′,’2010-03-06′,’08:00′,’17:00’)
Also please keep in mind that since this also considers time part the above call would calculate total hours from 01-01-2010 08:00 to 05-03-2010 17:00

Published by dataplatformcentral

7 thoughts on “Calculating business hours”

  1. This is a very useful function however if the Startdate and Enddate are the same day it returns an incorrect value.

  5. Thanks for this useful code! This has saved us a few hours of head scratching! I'm using this to calculate minutes, however, if the dates are the same, this returns 0 minutes. Thanks for your help

  7. Thank you for sharing this function! I have found a small issue…when you run the following (same start and end date that have times outside of the business hours) it will return 9 instead of 0.

    SELECT dbo.GetBusinessHours('05/03/2010 18:00', '05/03/2010 20:00', '08:00', '17:00'))

Comments are closed.