Calculate business hours between two dates

Question

How can I calculate business hours between two dates? For example we have two dates; 01/01/2010 15:00 and 04/01/2010 12:00 And we have working hours 09:00 to 17:00 in weekdays How can I calculate working hours with sql?

Answer 1

Baran's answer fixed and modified for SQL 2005

SQL 2008 and above:

-- ============================================= -- Author:      Baran Kaynak (modified by Kodak 2012-04-18) -- Create date: 14.03.2011 -- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar. -- ============================================= CREATE FUNCTION [dbo].[WorkTime]  (     @StartDate DATETIME,     @FinishDate DATETIME ) RETURNS BIGINT AS BEGIN     DECLARE @Temp BIGINT     SET @Temp=0      DECLARE @FirstDay DATE     SET @FirstDay = CONVERT(DATE, @StartDate, 112)      DECLARE @LastDay DATE     SET @LastDay = CONVERT(DATE, @FinishDate, 112)      DECLARE @StartTime TIME     SET @StartTime = CONVERT(TIME, @StartDate)      DECLARE @FinishTime TIME     SET @FinishTime = CONVERT(TIME, @FinishDate)      DECLARE @WorkStart TIME     SET @WorkStart = '09:00'      DECLARE @WorkFinish TIME     SET @WorkFinish = '17:00'      DECLARE @DailyWorkTime BIGINT     SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)      IF (@StartTime<@WorkStart)     BEGIN         SET @StartTime = @WorkStart     END     IF (@FinishTime>@WorkFinish)     BEGIN         SET @FinishTime=@WorkFinish     END     IF (@FinishTime<@WorkStart)     BEGIN         SET @FinishTime=@WorkStart     END     IF (@StartTime>@WorkFinish)     BEGIN         SET @StartTime = @WorkFinish     END      DECLARE @CurrentDate DATE     SET @CurrentDate = @FirstDay     DECLARE @LastDate DATE     SET @LastDate = @LastDay      WHILE(@CurrentDate<=@LastDate)     BEGIN                IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)         BEGIN             IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)             BEGIN                 SET @Temp = @Temp + @DailyWorkTime             END             --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes             ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)             BEGIN                 SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)             END              ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)             BEGIN                 SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)             END             --IF it starts and finishes in the same date             ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)             BEGIN                 SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)             END         END         SET @CurrentDate = DATEADD(day, 1, @CurrentDate)     END      -- Return the result of the function     IF @Temp<0     BEGIN         SET @Temp=0     END     RETURN @Temp  END 

SQL 2005 and below:

-- ============================================= -- Author:      Baran Kaynak (modified by Kodak 2012-04-18) -- Create date: 14.03.2011 -- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar. -- ============================================= CREATE FUNCTION [dbo].[WorkTime]  (     @StartDate DATETIME,     @FinishDate DATETIME ) RETURNS BIGINT AS BEGIN     DECLARE @Temp BIGINT     SET @Temp=0      DECLARE @FirstDay DATETIME     SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))      DECLARE @LastDay DATETIME     SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))      DECLARE @StartTime DATETIME     SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)      DECLARE @FinishTime DATETIME     SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)      DECLARE @WorkStart DATETIME     SET @WorkStart = CONVERT(DATETIME, '09:00', 8)      DECLARE @WorkFinish DATETIME     SET @WorkFinish = CONVERT(DATETIME, '17:00', 8)      DECLARE @DailyWorkTime BIGINT     SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)      IF (@StartTime<@WorkStart)     BEGIN         SET @StartTime = @WorkStart     END     IF (@FinishTime>@WorkFinish)     BEGIN         SET @FinishTime=@WorkFinish     END     IF (@FinishTime<@WorkStart)     BEGIN         SET @FinishTime=@WorkStart     END     IF (@StartTime>@WorkFinish)     BEGIN         SET @StartTime = @WorkFinish     END      DECLARE @CurrentDate DATETIME     SET @CurrentDate = @FirstDay     DECLARE @LastDate DATETIME     SET @LastDate = @LastDay      WHILE(@CurrentDate<=@LastDate)     BEGIN                IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)         BEGIN             IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)             BEGIN                 SET @Temp = @Temp + @DailyWorkTime             END             --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes             ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)             BEGIN                 SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)             END              ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)             BEGIN                 SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)             END             --IF it starts and finishes in the same date             ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)             BEGIN                 SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)             END         END         SET @CurrentDate = DATEADD(day, 1, @CurrentDate)     END      -- Return the result of the function     IF @Temp<0     BEGIN         SET @Temp=0     END     RETURN @Temp  END 

Answer 2

I know this is post is very old but here is a function I wrote recently to calculate Business Hours/Minutes between any two events. It also takes into account any holidays which must be defined in a table.

The function returns the interval in minutes - you can divide by 60 to get hours as required.

This has been tested on SQL Server 2008. Hope it helps someone.

Create Function GetWorkingMin(@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int AS Begin     Declare @WorkMin int = 0   -- Initialize counter     Declare @Reverse bit       -- Flag to hold if direction is reverse     Declare @StartHour int = 9   -- Start of business hours (can be supplied as an argument if needed)     Declare @EndHour int = 17    -- End of business hours (can be supplied as an argument if needed)     Declare @Holidays Table (HDate DateTime)   --  Table variable to hold holidayes      -- If dates are in reverse order, switch them and set flag     If @StartDate>@EndDate      Begin         Declare @TempDate DateTime=@StartDate         Set @StartDate=@EndDate         Set @EndDate=@TempDate         Set @Reverse=1     End     Else Set @Reverse = 0      -- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema)     Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where COUNTRYCODE=@Country and HDATE>=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)      If DatePart(HH, @StartDate)<@StartHour Set @StartDate = DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate))  -- If Start time is less than start hour, set it to start hour     If DatePart(HH, @StartDate)>=@EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) -- If Start time is after end hour, set it to start hour of next day     If DatePart(HH, @EndDate)>=@EndHour+1 Set @EndDate = DateAdd(hour, @EndHour, DateDiff(DAY, 0, @EndDate)) -- If End time is after end hour, set it to end hour     If DatePart(HH, @EndDate)<@StartHour Set @EndDate = DateAdd(hour, @EndHour-24, DateDiff(DAY, 0, @EndDate)) -- If End time is before start hour, set it to end hour of previous day      If @StartDate>@EndDate Return 0      -- If Start and End is on same day     If DateDiff(Day,@StartDate,@EndDate) <= 0     Begin         If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If day is between sunday and saturday             If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If day is not a holiday                 If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) -- Calculate difference             Else Return 0         Else Return 0     End     Else Begin         Declare @Partial int=1   -- Set partial day flag         While DateDiff(Day,@StartDate,@EndDate) > 0   -- While start and end days are different         Begin             If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7    --  If this is a weekday             Begin                 If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If this is not a holiday                 Begin                     If @Partial=1  -- If this is the first iteration, calculate partial time                     Begin                          Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))                         Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate))                          Set @Partial=0                      End                     Else Begin      -- If this is a full day, add full minutes                         Set @WorkMin=@WorkMin + (@EndHour-@StartHour)*60                                 Set @StartDate = DATEADD(DD,1,@StartDate)                     End                 End                 Else Set @StartDate = DATEADD(DD,1,@StartDate)               End             Else Set @StartDate = DATEADD(DD,1,@StartDate)         End         If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If last day is a weekday             If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0   -- And it is not a holiday                 If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)     End      If @Reverse=1 Set @WorkMin=-@WorkMin     Return @WorkMin End