Hello,

I found a link to calculating business hours which was helpful. The function is as shown below and the url is http://pratchev.blogspot.com/2008/01/calculating-work-hours.html 

CREATE FUNCTION dbo.CalcWorkHours (

 @start_date DATETIME,

 @end_date DATETIME)

RETURNS INT

AS

BEGIN

  RETURN

  (SELECT ((total_days / 7) * 5 + total_days % 7 -

           CASE WHEN 6 BETWEEN start_weekday AND end_weekday

                THEN 1 ELSE 0 END -

           CASE WHEN 7 BETWEEN start_weekday AND end_weekday

                THEN 1 ELSE 0 END) * 8

    FROM (SELECT total_days, start_weekday,

                start_weekday + total_days % 7 - 1

          FROM (SELECT DATEDIFF(day, @start_date, @end_date) + 1,

                       DATEPART(WEEKDAY, @start_date + @@DATEFIRST - 1)

               ) AS T(total_days, start_weekday)

        ) AS D(total_days, start_weekday, end_weekday)

  );

END

GO

However the function doesnt return correctly when I pass specific paramaters:

select dbo.CalcWorkHours('2014-05-14 15:46:16.000','2014-05-14 19:21:41.000') 

returns 8 instead of 4

can someone tell me why is it not returning it correctly?

Thanks

The code only looks at at the number of business days and then multiplies that number by 8(hrs), you're always going to return 8 given two values on the same date, no matter what the time portion of your datetime.

If you want a more granular solution, have a look at this:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/88bb6327-c8c7-471c-b15f-3067bf0c6117/i-know-another-calculate-business-hours-between-two-dates-question?forum=transactsql

• Proposed as answer by Marcelo Vollbrecht 3 hours 14 minutes ago

I am trying to use this function inside a very huge query and wanted to pass startdate and enddate parameters to this function to get actual business hours.

My logic was to get the number of days first and if greater than 0 I was using a case statement and passing startdate and enddate to this function else using datediff(hh,startdate,edndate). 

Does any modification to the above function achieve the accurate business hours?

Thanks

I am trying to use this function inside a very huge query and wanted to pass startdate and enddate parameters to this function to get actual business hours.

My logic was to get the number of days first and if greater than 0 I was using a case statement and passing startdate and enddate to this function else using datediff(hh,startdate,edndate). 

Does any modification to the above function achieve the accurate business hours?

Thanks

how do you calculate business hours--is it between 8:00 am to 5:00 am. do you exclude lunch hr or include them (9 hrs a day) 

so, if start date is 03/25/2015 11:30 am end date is 03/26/2015 3 pm...can you tell us your business hrs

Business hours 9:00AM to 6:00PM and I would include lunch hours and count them as 9hrs.

That would be 13.5 hours.

The dates format would be ('2014-05-14 15:46:16.000','2014-05-14 19:21:41.000') 

thanks