I_play_with_DNA
asked on
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
@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
sorry i shouldnt have thought it was that easy.
Add to that
select @minutesUsed=datediff(minu te,@newsta rtdate,@ne wenddate)
Select @minutesUsed=(@minutesUsed -(case when @StartDate<>@EndDate then datediff(day,@startdate,@e nddate)*90 0))
this will remove the after hours minutes between days. You can also do a similar function to check for weekends that have passed.
Add to that
select @minutesUsed=datediff(minu
Select @minutesUsed=(@minutesUsed
this will remove the after hours minutes between days. You can also do a similar function to check for weekends that have passed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,@newstartd