Determining the number of business days between two date fields

Question

Post reply

Determining the number of business days between two date fields

mltucker

SSC Rookie

Points: 37
More actions
June 3, 2011 at 2:58 pm

#252261

Hello, everyone.
First, let me apologize up front; I am both a newbie here, and admittedly self taught (OJT, in a way :-D). Even so, I have searched quite a bit, both on here and else where, and while I have seen some similar questions, nothing has been an actual solution to my issue.
I am using Report Builder 3.0, and I have created a report that, among other things, determines the number of days between two date fields, RequestDate and CompletedDate. However, the business has decided that it would be more useful if this were presented in business days. I currently arrive at the difference using (DateDiff(DateInterval.Minute,Fields!RequestDate.Value,Fields!CompleteDate.Value) / 1440), but I cannot find anything that helps me get from here to there, if you will.:crazy:
Any assistance anyone can provide would be GREATLTY appreciated.
Thanks,
Michael

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Here is group of date calculations that you might find very useful

https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

A question. You must now use "Business days", does that exclude holidays? If so search on SSC for the use of a calendar table, as an example that you may find useful

http://www.sqlservercentral.com/articles/T-SQL/70743/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

mltucker SSC Rookie Points: 37 More actions · Answer 2

Thanks, Ron!

Those are useful in general, but I am not seeing how I could apply those here. In laymen's terms, I am looking to "simply" not count Saturdays and Sundays. I have seen several examples of the calendar table you have mentioned, and was hoping to avoid that solution as I am reporting statistics, but am not a DBA. Thus, creating tables is something I would rather not tackle, if at all possible.

Again, thanks for any input, though.

mltucker SSC Rookie Points: 37 More actions · Answer 3

I actually was given the answer by Nehemiah Willis here.

While it does not account for holidays, it is perfect just for eliminating weekends.

you can do this in custom code:
Function business_days(ByVal StartDate As DateTime, ByVal EndDate As DateTime) As Integer
Dim Bus As Integer
Dim temp As DateTime
Bus = 0
temp = StartDate
While (temp <= EndDate)
If (temp.DayOfWeek <> DayOfWeek.Saturday And temp.DayOfWeek <> DayOfWeek.Sunday) Then
Bus += 1
End If
temp = DateAdd("d", 1, temp)
End While
Return Bus
End Function
then reference your code by doing something like:
=Code.business_days(Parameters!fromdate.Value,Parameters!todate.Value)

I hope this helps anyone else who might have had the same question.:-D