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 put 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 |
|
