# Calculating Working Hours

,

Calculating working hours can be painful due to all the variances that they imply. This function will calculate working hours between 2 dates which might include time. There's an option to use a holidays table that will vary in each environment.

```/*
Programmer: Luis Cazares
Date: 2014-05-22
Purpose: This function will return working hours between given 2 dates.
This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM.
This function also assumes that the working hours are between 7:30 AM and 4 PM.
This function was inspired by Goran Borojevic
*/CREATE function [dbo].[CalcWorkingHours] (@StartDate datetime, @EndDate datetime)
RETURNS table
AS RETURN
- (DATEDIFF(wk,@StartDate,@EndDate)*2) --Subtact 2 days for each full weekend
- (1-SIGN(DATEDIFF(dd,6,@StartDate)%7)) --If StartDate is a Sunday, Subtract 1
- (1-SIGN(DATEDIFF(dd,5,@EndDate)  %7))) * 465) --This will give us full days minus one that we'll complete with following operations
+ (SELECT CASE  WHEN @StartDate <= DATEADD(MI, 450, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))
THEN 465 --if Start Date is earlier than 7:30 then it counts as full day
THEN 0 --if Start Date is later than 16:00 then it won't count
ELSE DATEDIFF(MI, @StartDate, DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @StartDate), 0))) --Minutes between start date and 16:00
-
THEN 15 --If it's earlier than the break, substract whole break
--If it's earlier than the end of the break, substract corresponding minutes
ELSE 0
END
-
THEN 30 --If it's earlier than lunch, substract whole lunch time
--If it's earlier than the end of lunch time, substract corresponding minutes
ELSE 0
END
END

+ CASE WHEN @EndDate <= DATEADD(MI, 450, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0))
THEN 0 --if End Date is earlier than 7:30 then it won't count
THEN 465 --if End Date is later than 16:00 then it counts as full day
ELSE DATEDIFF(MI, DATEADD(MI, 960, DATEADD(DD, DATEDIFF(DD, 0, @EndDate), 0)), @EndDate) --Minutes between 7:30 and end date
-
THEN 15 --If it's later than the break, substract whole break
--If it's later than the start of the break, substract corresponding minutes
ELSE 0
END
-
THEN 30 --If it's later than lunch, substract whole lunch time
--If it's later than the start of lunch time, substract corresponding minutes
ELSE 0
END
END
WHERE @StartDate <= @EndDate)
/* Uncomment to use holidays table
- ((SELECT count(*)
FROM holidaystable
WHERE [Date] BETWEEN @StartDate AND @EndDate) * 7.75)
*/    ,0) / 60.0 AS WorkingHours```

## Rate

4.67 (3)

You rated this post out of 5. Change rating

## Rate

4.67 (3)

You rated this post out of 5. Change rating