CODEWITHSUNDEEP
sqlsql-server
rigamonk
rigamonk

Reputation: 1181

Get number of hours excluding weekends

I would like to get the number of hours elapsed between 2 datetimes, but excluding weekends.

So lets say I had 2014-03-28 07:45:00 (which is a Friday) and 2014-04-04 09:45:00 (which is a Friday) I want to figure out how many hours have elapsed between the 2 dates but exclude the weekends.

I have no idea how to proceed, though i did find a link on finding weekdays at http://www.codersrevolution.com/blog/SQL-Server-How-Many-WorkWeek-Days-In-Date-Range

Upvotes: 0

Views: 2771

Answers (1)

Ron Smith
Ron Smith

Reputation: 3266

Create a Function:

create function f_hour_diff (@date1 datetime,@date2 datetime)
returns int
as
begin
    -- if @date1 happens on the weekend, set to next Monday 12AM
    if datepart(weekday,@date1) = 1
        set @date1 = dateadd(day,datediff(day,0,@date1),1)
    if datepart(weekday,@date1) = 7
        set @date1 = dateadd(day,datediff(day,0,@date1),2)

    -- if @date2 happens on the weekend, set to previous Saturday 12AM
    -- to count all of Friday's hours
    if datepart(weekday,@date2) = 1
        set @date2 = dateadd(day,datediff(day,0,@date2),-2)
    if datepart(weekday,@date2) = 7
        set @date2 = dateadd(day,datediff(day,0,@date2),-1)

    -- return number of hours between @date1 and @date1
    -- subracting the number of weeks between * 48 to take out weekends
    declare @return int
    set @return = datediff(hour,@date1,@date2)-(datediff(week,@date1,@date2)*48)

    -- Return the result of the function
    return @return

end
go

Call the Function:

select dbo.f_hour_diff('2014-03-28 07:45:00','2014-04-04 09:45:00')

Upvotes: 2

Related Questions