how to calculate business hours between days in sqlserver 2008?
please anybody knows tell me very urgent ly.
thanks in advance.
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Dhirendra MisraPosted Jan 7, 2014, 1:38 AM
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)
)