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