get business hours

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[GetBusinessMinutes] (
@startDate datetime,
@endDate datetime
)
returns int
as
/*
Purpose: Returns the number minutes over business days between @StartDate and @EndDate

Note: This function does not account for holidays, actual open hours (9am-5pm ET), or internationalization.

Modification History
Date By Notes
----------- --------------- ---------------------------------------
10/06/2008 B.Huntley created
10/08/2008 B.Huntley Fixed function to exclude weekends
12/22/2008 C.Gaden Fixed problem when start date was on a weekend
Fixed problem when end date was on a weekend
Fixed problem for daylight savings overlap

*/
BEGIN
DECLARE @daysBetween INT
DECLARE @weekendDays INT
DECLARE @count INT
DECLARE @evalDate DATETIME
declare @returnValue int

SELECT @daysBetween = 0
SELECT @weekendDays = 0
SELECT @count = 0

if (datepart(dw, @startDate) = 1 or datepart(dw, @startDate) = 7) and
(datepart(dw, @endDate) = 1 or datepart(dw, @endDate) = 7) and
(datediff(d, @startDate, @endDate) <= 2)
-- start and end date are on the same weekend; open days is zero
select @returnValue = 0
else
begin
-- account for @startDate occurring on a Saturday or Sunday (do not count these days)
-- if start date is on a weekend make it set to 1 minute after midnight on the following Monday
select @startDate =
case when (DATEPART(dw, @startDate) = 1) then convert(datetime, convert(varchar, month(dateadd(day, 1, @startDate))) + '/' + convert(varchar, day(dateadd(day, 1, @startDate))) + '/' + convert(varchar, year(dateadd(day, 1, @startDate))) + ' 12:01:00 am')
when (DATEPART(dw, @startDate) = 7) then convert(datetime, convert(varchar, month(dateadd(day, 2, @startDate))) + '/' + convert(varchar, day(dateadd(day, 2, @startDate))) + '/' + convert(varchar, year(dateadd(day, 2, @startDate))) + ' 12:01:00 am')
else @startDate
end

-- account for @endDate occurring on a Saturday or Sunday (do not count these days)
-- if end date is on a weekend make it set to 1 minute before midnight on the previous Friday
select @endDate =
case when (DATEPART(dw, @endDate) = 1) then convert(datetime, convert(varchar, month(dateadd(day, -2, @endDate))) + '/' + convert(varchar, day(dateadd(day, -2, @endDate))) + '/' + convert(varchar, year(dateadd(day, -2, @endDate))) + ' 11:59:00 pm')
when (DATEPART(dw, @endDate) = 7) then convert(datetime, convert(varchar, month(dateadd(day, -1, @endDate))) + '/' + convert(varchar, day(dateadd(day, -1, @endDate))) + '/' + convert(varchar, year(dateadd(day, -1, @endDate))) + ' 11:59:00 pm')
else @endDate
end

-- return days between start & end, inclusive of start date
SELECT @daysBetween = DATEDIFF(DAY, @startDate, @endDate) + 1

-- check each day to see if it is a weekend
WHILE @count < @daysBetween
BEGIN
SELECT @evalDate = dateadd(day, @count, @startDate)
IF ((DATEPART(dw, @evalDate) = 1) OR (DATEPART(dw, @evalDate) = 7))
SELECT @weekendDays = @weekendDays + 1
SELECT @count = @count + 1
END

-- calculate number of open minutes (less weekends)
select @returnValue = DATEDIFF(mi, @startDate, @endDate) - @weekendDays * 1440

-- check if negative value occurred; this may happen during daylight savings time-shift
if @returnValue < 0
select @returnValue = 0
end

return @returnValue
END