Calculate business hours between two dates
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
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
An alternative solution from @Pavanred's, coming at things from a more data-based angle:
Create a table with all the dates you want to consider in it. For each day, set a number of working hours, like so:
WorkingDate Hours Comment
=========== ===== ==================
1 Jan 2011 0 Saturday
2 Jan 2011 0 Sunday
3 Jan 2011 0 Public Holiday
4 Jan 2011 8 Normal working day
5 Jan 2011 8 Normal working day
-- and so on, for all the days you want to report on.
This will take a small amount of setting up -- you can pre-populate it for weeks versus weekends automatically, then adjust for public holidays, etc, as necessary.
But, what you lose in the setting up, you gain in ease of querying:
SELECT
SUM(Hours)
FROM
working_days
WHERE
WorkingDate BETWEEN @StartDate AND @EndDate
...and this can work out as an easier approach if you need to start adding more complicated rules for what defines a working day, or if your working hours vary depending on the day, etc.
It also makes the rules more easily "editable", as you don't need to change any actual code to change the definitions of a working day, add public holidays, etc.