working out the number of working hours between two date times

[jamiec1]

this is a problem i have been trying to get my head arround but to no avail ..
any help is appreciated.
jamie

 

[jamiec1]

ps it has to take into account :
working week mon to fri
each day 9 to 5

 

[Josh01]

I know that it doesnt fully take in everything that you need it to but you can use the DATEDIFF function to workout the number of hours between two dates:

Select DATEDIFF ( hour, startdate , enddate )


Regards

Josh

 

[tlbroadbent]

Which version of SQL Server? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[welshone]

I am using SQL 7 and would like to know how to do this.

cheers.

 

[tlbroadbent]

You can use Datediff but using the Hour will be inaccurate as Datediff will return the difference of 1 hour between 8:59AM and 9:01AM. Use minute instead and divide by 60.

select datediff(minute,'2/4/02 08:59','2/4/02 09:01')/60.

If the dates can span days and you want to know the number of working hours in that date range, you'll need to create a stored procedure to perform the calculations. You may need to modify to meet your needs but this should give you an idea of how to start.

------------------------------------
Create procedure CalculateWorkHours
@sd datetime,
@ed Datetime,
@workhrs decimal (10,3) Output
As

declare @cd datetime

set @sd ='1/25/02 09:00:00'
set @ed=getdate()

Set @cd=convert(char(11), @sd)
If datepart(dw, @sd) Between 2 and 6
Begin
If datepart(hour,@sd) Between 9 and 17
Begin
Set @WorkHrs=Datediff(minute, @sd, @cd + '17:00:00')/60.
End
Else
If datepart(hour,@cd)>17
Begin
Set @WorkHrs=0
End
Else
Begin
Set @WorkHrs=8
End
End
Else
Begin
Set @WorkHrs=0
End

Set @cd=@cd+1

While @cd<convert(char(11), @ed)
Begin
If datepart(dw,@cd) Between 2 and 6
Begin
Set @WorkHrs = @WorkHrs + 8
End
Set @cd=@cd+1
End

If datepart(dw, @ed) Between 2 and 6
Begin
If datepart(hour,@sd) Between 9 and 17
Begin
Set @WorkHrs=@workhrs+Datediff(minute, @ed, @cd + '17:00:00')/60.
End
Else
If datepart(hour,@cd)>17
Begin
Set @WorkHrs=@workHrs+0
End
Else
Begin
Set @WorkHrs=@WorkHrs+8
End
End

------------------------------

Call the stored procedure:

Declare @workhours Decimal(10,3)

exec CalculateWorkHours
<start date>, <end date>, @workhours Output

Print @workhours Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.