Calculate elapsed business hours - SQL 2008

Our business is open 9-5, Monday to Friday.  I am using the SQL function in the attached text file to calculate the elapsed business hours between two datetimes.  The variables are as follows:
@startdate - the start datetime of the period (datetime)
@enddate - the end date of the period (datetime)
@starttime - the start of the business day (vchar(9))
@endtime - the end of the business day (vchar(9))

The function is called with a SQL statement that passes the values of these variables to the function.

Does anyone have any idea how I would go about modifying this function to exclude Saturdays and Sundays from the calculation?

Thanks!
business-hours.txt
I_play_with_DNAAsked:
Who is Participating?
 
rmm2001Connect With a Mentor Commented:
Put in a where DATEPART(weekday, @startdate) NOT IN (1, 7) (1 is sunday...7 is saturday)
0
 
OriNetworksCommented:
FIrst I would ask why the times are separated from the dates.

Create a new combined date/time
e.g.
@startdate = '1/1/2010'
@enddate = ' 1/2/2010'
@starttime = '9:00 am'
@endtime = '12:30 pm'

@newstartdate = '1/1/2010 9:00:00 am'
@newenddate = '1/2/2010 12:30:00 pm'

select datediff(minute,@newstartdate,@newenddate)/60 = the amount of hours spent
0
 
OriNetworksCommented:
sorry i shouldnt have thought it was that easy.

Add to that

select @minutesUsed=datediff(minute,@newstartdate,@newenddate)

Select @minutesUsed=(@minutesUsed-(case when @StartDate<>@EndDate then datediff(day,@startdate,@enddate)*900))

this will remove the after hours minutes between days. You can also do a similar function to check for weekends that have passed.
0
 
CboudrozConnect With a Mentor Commented:
add this in the where clause:

and DATEPART(dw,DateValue) not in (1,7)
0
 
OriNetworksConnect With a Mentor Commented:
Ithe datelart cannot be and otherwise it will evaluate as false for start or end date on weekends and not return a result. You would have to add a case statement with the datepart to subtract those days but this is not good either because it would not calculate weekends in between so oh have to use it in conjunction with a datediff to find out how many weekends are between those dates.
,
0
All Courses

From novice to tech pro — start learning today.