Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Each time we face a fear, we gain strength, courage, and confidence in the doing
 
Unknown
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

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

MAKE A COMMENT

Name *

Email Address *

Comment *