Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
A monk asked Yun-men, "What are the teachings of a lifetime?" Yun-men said to him, "An appropriate statement."
 
Zen Mondo
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
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

MAKE A COMMENT

Name *

Email Address *

Comment *