Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

get DATEDIFF excluding weekends using sql server

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.

like image 362
Sami Avatar asked Sep 12 '11 13:09

Sami


People also ask

How do I exclude weekends between two dates in SQL Server?

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.

How do I count weekdays between two dates in SQL?

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.

How do I find the number of weekends between two dates in SQL Server?

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.


1 Answers

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 
like image 116
Derek Kromm Avatar answered Sep 23 '22 12:09

Derek Kromm