03 April  How Do I Calculate the Number of Business Days Between Two Dates in SQL Reporting Services 2008?  
During a current report design consultancy using SQL Server Reporting Services 2008 I was asked "How do I calculate the No. business days between two dates i.e. excluding weekends?"
For those who use Microsoft Excel and Microsoft Access, if one wanted to
calculate the number of days between two dates, excluding the weekends,
one would use the NETWORKDAYS() function.
Solution
Unfortunately in SQL and hence SQL Server Reporting Services there is no such function, but one can still achieve the same using the DATEDIFF and DATEPART functions.
To calculate the No. Days between two dates, excluding weekends:
SELECT (DATEDIFF(dd, @startdate, @enddate) + 1)
(DATEDIFF(wk, @startdate, @enddate) * 2)
(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END) end As No_Days
If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us


1  Steven Carroll  23 May 
This is great and I use it, but how would we handle a holiday in this case? 
2  Julia  24 May 
Hi Steven
Thanks for your comments.
The only way to achieve this would be to use some SQL coding to firstly create a temporary table that holds all the holiday dates, then create a loop that tests to see how many of those dates occur, and then take this away from the the date range.
Julia

3  Lizzy  04 July 
Thanks for this , it works well for me and I have managed to tweak it for SSRS reports . However I am curious about the
SELECT (DATEDIFF(dd, @startdate, @enddate) + 1)
why +1 ? As I see it the difference between @startdate =19 June and @enddate = 26th June is 5 days but using the :
SELECT (DATEDIFF(dd, @startdate, @enddate) + 1)
it returns 6.

4  Julia  05 July 
Hi Lizzy,
The + 1 is used to include the @startdate as part of the count, within the SQL statement.
So for example is the @startdate = 05/07/2013 and the @enddate = 08/07/2012
Then, if you leave the + 1 the SQL statement, results in 2 days, if you remove the + 1, the SQL statement results in 1. 
5  Jason Dibelka  22 July 
Steven, to handle holidays, I used this same function and added a Holidays table with a holiday column for the date. From there, I simply added: "(Select Count(*) FROM Holidays Where Holiday >= @StartDate AND Holiday < @EndDate)" right after the second CASE statement.

6  Jason Dibelka  22 July 
Also, I also found this add, so I can't take credit for creating it. Unfortunately, I don't remember where I found the holiday fix. 
7  J C  03 November 
thanks for the help, this is exactly what i needed 
8  Steven Carroll  10 February 
Thanks, that worked for me. 
9  Hima  14 July 
Thanks alot. Its realy help. 
10  Julia  07 September 
Hi Hema,
The best way around that is to add a SELECT CASE statement at the beginning to test if the 2 dates are the same, and if they are return the value 0, otherwise do the DATEDIFF formula.
Hope this helps
Julia 
11  Danny  04 January 
The same function in MS Access (using a table for holidays) if you are ever in need
(DateDiff("d",[StartDate],[EndDate]))(DateDiff("ww",[Stardate],[EndDate])*2)(IIf(DatePart("w",[StartDate])=1,1,0))(IIf(DatePart("w",[EndDate])=7,1,0))(Select Count(*) FROM Holidays Where HolidayDate >= [StartDate] AND HolidayDate < [EndDate]) 
12  Sanjeet Prabhu  01 April 
This solution made my day.
Thanx a tonne... 
13  Komal  10 December 
Thanks for this solution. Its very interesting but I getting little confused.
(DATEDIFF(wk, @startdate, @enddate) * 2)
How this part of code works? and why we are doing * 2? 
14  Julia  10 December 
Hi Komal,
That part of the formula takes into consideration the Saturday and Sunday of the week, which are being cionsidered as non working days.
Julia 
15  Komal  10 December 
Hey Julia,
Thanks for explanation. But its working fine only when DATEDIFF(dd, @startdate, @enddate) is return +ve numbers and not for ve numbers. 
16  Julia  10 December 
Hi Komal,
Thanks for letting us know that it's working.
However this should not be creating negative numbers as the @startdate should always be less than or earlier than the @enddate!
If you are getting negative figures that means that the dates that you are using/entering are incorrect.
Cheers
Julia 
17  Komal  19 December 
If we are checking no of Saturday and sunday by using (DATEDIFF(wk, @startdate, @enddate) * 2). then why we are again checking for
(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END) . 
18  Julia  19 December 
This is a formula to calculate the number of working days between two dates.
If the user enters a a start date or end date that is a Saturday or Sunday, it should not be included in the calculation, and that is what the CASE statements corrects for.
Cheers
Julia 
19  Komal  19 December 
Hey Julia,
But here we are only checking start date for sunday and end date for saturday and not for both the dates 
20  Julia  19 December 
True!
Formula assumes Sunday comes before Saturday, as Sunday is the first day of the week, and the @startdate is always before @enddate
The formula can always be modified accordingly 
21  Komal  19 December 
also I observed that if No of days between two dates is 5 then it is returning me 1 for no of week. then How it is calculationg DATEDIFF(wk, @startdate, @enddate) working? 
22  Julia  19 December 
Komal,
I think you have misunderstood the function of this blog.
This blog highlights some of the solutions we have provided our clients either through training, consultancy or report writing.
As you seem to have a specific issue, this is not the correct forum, as we do not know what it is that you are doing to create the results you are getting.
I suggest if you are looking for a solution to your specific issue, then you use one of our training or consultancy solutions, and email us directly so that we can resolve it properly.
Julia 
23  Jane Lee  13 March 
When the @StartDate = @EndDate, I got no_days = 6. How to fix it?
Thanks 
24  Julia  13 March 
Hi Jane,
Can you please but the full equation/formula and the dates you are using, as you have not provided me with enough information to provide you with an answer.
Thanks
Julia 
25  Abhi MAlhotra  23 January 
Hi Want to understand the DATEDIFF(wk, @startdate, @enddate) * 2) this part 
26  Jay Patel  29 January 
Make sure you subtract 1. If you're expecting it to work like DATEDIFF, the days between Tuesday and Wednesday should be 1, not 2.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '1/17/19'
SET @EndDate = '1/18/19'
 CalendarDateDiff vs Business Date Diff
SELECT
DATEDIFF(d, @StartDate, @EndDate) AS CalendarDateDiff
, (DATEDIFF(dd, @StartDate, @EndDate) + 1)
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) AS CalendarDays 
27  Raven Johnson  13 June 
@jason, how/where do you add the table for the holidays? 
