find working hours between two date

CREATE function getWorkingHrsInMin(@datetime1 datetime,@datetime2datetime, @DailyWorkingHrs int)

returns int

as

begin

declare @time int

set @time=0

declare @tmpTbl table

(

Col_Date datetime,

Col_Day nvarchar(50),

WorkingHrs int

)

WHILE (@datetime1 <= @datetime2)

BEGIN

insert into @tmpTbl(Col_Date,Col_Day,WorkingHrs)

values (@datetime1, CASE DATEPART(weekday,@datetime1)

WHEN 1 THEN 'Sun'

WHEN 2 THEN 'Mon'

WHEN 3 THEN 'Tue'

WHEN 4 THEN 'Wed'

WHEN 5 THEN 'Thu'

WHEN 6 THEN 'Fri'

WHEN 7 THEN 'Sat'

END,

@DailyWorkingHrs)

SET @datetime1 = @datetime1 + 1

END

set @time=(select sum(WorkingHrs)*60 from @tmpTbl whereCol_Day not in('Sat','Sun'))

return @time

end

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

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

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

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

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

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

Call this function like this:

1st Parameter: StartDate

2nd Parameter: EndDate

3rd Parameter: Daily working Hours.

select dbo.getWorkingHrsInMin('7-Mar-2010','13-Mar-2010',8)

Result:

2400