This is some quick and dirty T-SQL that might help you. I'm adding up the total elapsed minutes between the two dates, then run a WHILE loop to increment the hours by 1, and decrement the minutes by 60, as long as the total minutes is greater than 60.

I've added some presumptive code in case the two dates aren't consecutive in production. It will add 480 minutes (presumed 8 hr work day) to each day between the two dates, excluding Saturdays and Sundays.

If anyone else out there has suggestions for a better way of doing this, I'm always open to constructive feedback.

I hope this helps,

Andre

-- Andre Ranieri 5/16/2012

DECLARE @Date1 datetime, @Date2 datetime -- INPUT PARAMETERS

SET @Date1 = '2012-05-14 12:07:00.000'

SET @Date2 = '2012-05-15 15:40:34.497'

DECLARE @TotMin int, @TotHrs INT -- TOTAL ELAPSED MINUTES

SET @TotMin = 0

SET @TotHrs = 0

DECLARE @FirstDateEnd DATETIME, @LastDateBegin DATETIME -- CALCULATED DATE FOR START AND END OF WORKING DAY

SET @FirstDateEnd = CAST(@Date1 AS DATE)-- Cast AS Date = Get Midnight Time

SET @FirstDateEnd = DATEADD (hh, 17, @FirstDateEnd)-- 17 hrs from midnight = 5 pm

SET @LastDateBegin = CAST(@Date2 AS DATE)-- Cast AS Date = Get Midnight Time

SET @LastDateBegin = DATEADD (mi, 510, @LastDateBegin)-- 510 minutes from midnight = 8:30 AM

SET @TotMin = (select datediff (mi, @date1, @FirstDateEnd) )

SET @TotMin = @TotMin + (select datediff (mi, @LastDateBegin, @date2) )

-- FOR EACH WORKING DAY BETWEEN @DATE1 AND @DATE2 INCREMENT TOTAL MINUTES BY 480 (PRESUMED 8 HR WORK DAY)

WHILE CAST(@Date1 as date) < DATEADD(dd, -1, CAST(@LastDateBegin AS date) )

BEGIN

IF DATEPART(dw, @Date1 ) NOT IN (1,7) -- EXCLUDE WEEKENDS (PRESUMED)

BEGIN

SET @TotMin = @TotMin + 480

END

SET @Date1 = DATEADD(dd, 1, @Date1)

END

-- Full credit to Lynn Here 🙂

SELECT CAST(@TotMin / 60 AS VARCHAR) + 'hr ' + RIGHT('0' + CAST(@TotMin % 60 AS VARCHAR),2) + 'min' AS Output