I am using this query to get time taken.
SELECT DATEDIFF(dd, ActualStartDate, ActualCompletionDate) AS TimeTaken FROM TableName
Now I want to exclude weekends and only include Mon-Fri as days counted.
You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query. And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection.
It's just that both @StartDate and @EndDate are included in the count. If you want Monday to Tuesday to count as 1 day, just remove the "+ 1" after the first DATEDIFF. Then you'll also get Fri->Sat=0, Fri->Sun=0, Fri->Mon=1.
The statement DATEDIFF(dd,@fromdate,@todate) + 1 gives the number of dates between the two dates. The statement DATEDIFF(wk,@fromdate,@todate) gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count. The next two statements excludes the day if it's a Saturday or Sunday.
Example query below, here are some details on how I solved it.
Using DATEDIFF(WK, ...)
will give us the number of weeks between the 2 dates. SQL Server evaluates this as a difference between week numbers rather than based on the number of days. This is perfect, since we can use this to determine how many weekends passed between the dates.
So we can multiple that value by 2 to get the number of weekend days that occurred and subtract that from the DATEDIFF(dd, ...)
to get the number of weekdays.
This doesn't behave 100% correctly when the start or end date falls on Sunday, though. So I added in some case logic at the end of the calculation to handle those instances.
You may also want to consider whether or not the DATEDIFF
should be fully inclusive. e.g. Is the difference between 9/10 and 9/11 1 day or 2 days? If the latter, you'll want to add 1 to the final product.
declare @d1 datetime, @d2 datetime select @d1 = '9/9/2011', @d2 = '9/18/2011' select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2) - case when datepart(dw, @d1) = 1 then 1 else 0 end + case when datepart(dw, @d2) = 1 then 1 else 0 end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With