CODEWITHSUNDEEP
ssrs-2008datediff
Lance
Lance

Reputation: 125

SSRS 2008 Datediff for Working Days

I have seen this question asked a lot and I cannot seem to find one clear answer about

"how to calculate business days only between two dates?"

The expression below will give me the total days but I am looking to exclude Saturday and Sunday.

=DateDiff("d",Parameters!STARTDATE.Value,Parameters!ENDDATE.Value)

I would appreciate specific help about how to accomplish this.

Thank you in advance.

Upvotes: 8

Views: 25507

Answers (4)

Theo Buitenhuis
Theo Buitenhuis

Reputation: 11

The easy way getting workingdays between two dates is:

DateDiff(DD, BeginDate, EndDate) - DateDiff(WK, Begindate, EndDate)*2

Upvotes: 1

Mohith
Mohith

Reputation: 1

Below code worked for me properly in all the scenarios

= (DateDiff(DateInterval.day,Parameters!BeginDate.Value,Parameters!EndDate.Value)+1)
- (DateDiff(DateInterval.WeekOfYear,Parameters!BeginDate.Value,Parameters!EndDate.Value)*2)
- IIF(Weekday(Parameters!BeginDate.Value,1) = 1,1,0)
- IIF(Weekday(Parameters!EndDate.Value,1) = 7,1,0)

Upvotes: 0

Pops
Pops

Reputation: 498

The SQL in the link (Number of working days between two dates) translated for SSRS: Hopefully this will give you a good place to start. Type this into the expression for the textbox.

=(DateDiff(DateInterval.day,Parameters!STARTDATE.Value,Parameters!ENDDATE.Value)+1)
-(DateDiff(DateInterval.WeekOfYear,Parameters!STARTDATE.Value,Parameters!ENDDATE.Value)*2)
-(iif(Weekday(Parameters!STARTDATE.Value) = 7,1,0)
-(iif(Weekday(Parameters!ENDDATE.Value) = 6,1,0))-1)

Upvotes: 3

Charles M.
Charles M.

Reputation: 71

This code is not exactly correct. A year can start or end with either a Saturday or a Sunday. For example, 2011 starts on a Saturday and ends on a Saturday. January 1st & 2nd, 2011 are Saturday and Sunday respectively and Dec 31st, 2011 is also a Saturday. The above code does not account for this scenario. The code below is correct:

= (DateDiff(DateInterval.day,Parameters!BeginDate.Value,Parameters!EndDate.Value)+1)
- (DateDiff(DateInterval.WeekOfYear,Parameters!BeginDate.Value,Parameters!EndDate.Value)*2) 
- IIF(Weekday(Parameters!BeginDate.Value,1) = 1,1,0)
- IIF(Weekday(Parameters!BeginDate.Value,1) = 7,1,0)
- IIF(Weekday(Parameters!EndDate.Value,1) = 1,1,0)
- IIF(Weekday(Parameters!EndDate.Value,1) = 7,1,0)

Upvotes: 7

Related Questions