how to calculate business hours between days on sql server 2008r2

Question

how to calculate business hours between days in sqlserver 2008? please anybody knows tell me very urgent ly. thanks in advance.

Dhirendra Misra · Answer

Hello Santhosh Try this DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME DECLARE @BusinessHours INT SET @StartDate = '01/01/2014' SET @EndDate = '01/07/2014' SET @BusinessHours = 8 SELECT @BusinessHours * ( (DATEDIFF(dd @StartDate @EndDate) + 1) -(DATEDIFF(wk @StartDate @EndDate) * 2) -(CASE WHEN DATENAME(dw @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw @EndDate) = 'Saturday' THEN 1 ELSE 0 END) )