As part of working out SLA’s by using the working hours between two dates we struggled to find a working solution, one of our developers built a nice function that does just that;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].udctGetWorkingMins(@from DATETIME, @to DATETIME,@daystartmins INT NULL,@dayendmins INT=NULL)
RETURNS INT
AS
BEGIN
-- Set working day boundary if not provided
IF @daystartMins IS NULL SET @daystartmins = (8 * 60) -- 8am
IF @dayEndMins IS NULL SET @dayendmins = (17 * 60)+15 -- 5:15pm
-- Get time portion in minutes since midnight
DECLARE @startMins INT = datediff(minute,'00:00:00',convert(time,@from))
DECLARE @endMins INT = datediff(minute,'00:00:00',convert(time,@to))
-- Limit time portion to working day boundary provided
SET @startMins = CASE WHEN @startMins < @daystartmins then @daystartmins
WHEN @startMins > @dayendmins then @dayendmins
ELSE @startMins END
SET @endMins = CASE WHEN @endMins < @daystartmins then @daystartmins
WHEN @endMins > @dayendmins then @dayendmins
ELSE @endMins END
DECLARE @betweenMins INT =0 -- holds the number of minutes between the two dates
DECLARE @currEqFrom INT =0 -- are we the From date
DECLARE @currEqTo INT=0 -- are we the To date
DECLARE @current DATETIME = CONVERT(date,@from)
WHILE @current <= CONVERT(date,@to)
BEGIN
IF DATENAME(dw,@current) = 'Saturday' or DATENAME(dw,@current)='Sunday'
BEGIN
set @current=dateadd(d,1,@current)
CONTINUE
END
-- Set flag for which day we are on
SET @currEqFrom = CASE WHEN @current=CONVERT(date,@from) THEN 1 ELSE 0 END
SET @currEqTo = CASE WHEN @current=CONVERT(date,@to) THEN 1 ELSE 0 END
IF @currEqFrom=1 and @currEqTo=1 SET @betweenMins=@betweenMins + (@endMins-@startMins)
IF @currEqFrom=1 and @currEqTo=0 SET @betweenMins=@betweenMins + (@dayendmins-@startMins)
IF @currEqFrom=0 and @currEqTo=1 SET @betweenMins=@betweenMins + (@endMins-@daystartmins)
IF @currEqFrom=0 and @currEqTo=0 SET @betweenMins=@betweenMins + (@dayendmins-@daystartmins)
set @current=dateadd(d,1,@current)
END
RETURN @betweenMins
END






