Hi,

Is there an easy / built-in way of calculating the number of hours between
two dates, but ignoring weekends?

E.g.

To calculate the number of hours between midnight this morning and midnight
on Monday morning, I might use:

SELECT DATEDIFF(hh, '1 Jul 2005 00:00:00', '4 Jul 2005 00:00:00')

returns 72.

However, the hours of Saturday and Sunday need to be ignored i.e. the answer
should be 24.

I could write a function or SP to do this using a cursor, but wondered if
there was a neater solution?

Any assistance gratefully received.

Mark

This code calculates business dates.  You can adapt to suit:

create function dbo.BusinessDays
(
  @start datetime
, @end   datetime
)
returns int
as
begin
return

  datediff (dd, @start, @end) -
  datediff (wk, @start, @end) * 2 -
  case
    when datepart (dw, @end)   = 7 then
      case
        when datepart (dw, @start) = 7 then 0
        else 1
      end
    when datepart (dw, @start) = 7 then -1
    else 0
  end
end
go

select
  dbo.BusinessDays ('1Apr01', '14Apr01')
go

drop function dbo.BusinessDays
go


Is there an easy / built-in way of calculating the number of hours between
two dates, but ignoring weekends?

E.g.

To calculate the number of hours between midnight this morning and midnight
on Monday morning, I might use:

SELECT DATEDIFF(hh, '1 Jul 2005 00:00:00', '4 Jul 2005 00:00:00')

returns 72.

However, the hours of Saturday and Sunday need to be ignored i.e. the answer
should be 24.

I could write a function or SP to do this using a cursor, but wondered if
there was a neater solution?

Any assistance gratefully received.

Mark

Thanks very much.

Case Statement?
View Explaination
Index Question
Warning: Null value is eliminated (causing sproc to fail)
Query urgent