Calculate business hours between two dates

Baran's answer fixed and modified for SQL 2005

SQL 2008 and above:

-- =============================================
-- Author:      Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime] 
(
    @StartDate DATETIME,
    @FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay DATE
    SET @FirstDay = CONVERT(DATE, @StartDate, 112)

    DECLARE @LastDay DATE
    SET @LastDay = CONVERT(DATE, @FinishDate, 112)

    DECLARE @StartTime TIME
    SET @StartTime = CONVERT(TIME, @StartDate)

    DECLARE @FinishTime TIME
    SET @FinishTime = CONVERT(TIME, @FinishDate)

    DECLARE @WorkStart TIME
    SET @WorkStart = '09:00'

    DECLARE @WorkFinish TIME
    SET @WorkFinish = '17:00'

    DECLARE @DailyWorkTime BIGINT
    SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END
    IF (@FinishTime<@WorkStart)
    BEGIN
        SET @FinishTime=@WorkStart
    END
    IF (@StartTime>@WorkFinish)
    BEGIN
        SET @StartTime = @WorkFinish
    END

    DECLARE @CurrentDate DATE
    SET @CurrentDate = @FirstDay
    DECLARE @LastDate DATE
    SET @LastDate = @LastDay

    WHILE(@CurrentDate<=@LastDate)
    BEGIN       
        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + @DailyWorkTime
            END
            --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
            END
            --IF it starts and finishes in the same date
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END

SQL 2005 and below:

-- =============================================
-- Author:      Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime] 
(
    @StartDate DATETIME,
    @FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
    DECLARE @Temp BIGINT
    SET @Temp=0

    DECLARE @FirstDay DATETIME
    SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))

    DECLARE @LastDay DATETIME
    SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))

    DECLARE @StartTime DATETIME
    SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)

    DECLARE @FinishTime DATETIME
    SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)

    DECLARE @WorkStart DATETIME
    SET @WorkStart = CONVERT(DATETIME, '09:00', 8)

    DECLARE @WorkFinish DATETIME
    SET @WorkFinish = CONVERT(DATETIME, '17:00', 8)

    DECLARE @DailyWorkTime BIGINT
    SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)

    IF (@StartTime<@WorkStart)
    BEGIN
        SET @StartTime = @WorkStart
    END
    IF (@FinishTime>@WorkFinish)
    BEGIN
        SET @FinishTime=@WorkFinish
    END
    IF (@FinishTime<@WorkStart)
    BEGIN
        SET @FinishTime=@WorkStart
    END
    IF (@StartTime>@WorkFinish)
    BEGIN
        SET @StartTime = @WorkFinish
    END

    DECLARE @CurrentDate DATETIME
    SET @CurrentDate = @FirstDay
    DECLARE @LastDate DATETIME
    SET @LastDate = @LastDay

    WHILE(@CurrentDate<=@LastDate)
    BEGIN       
        IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
        BEGIN
            IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + @DailyWorkTime
            END
            --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
            END

            ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
            END
            --IF it starts and finishes in the same date
            ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
            BEGIN
                SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
            END
        END
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
    END

    -- Return the result of the function
    IF @Temp<0
    BEGIN
        SET @Temp=0
    END
    RETURN @Temp

END

I know this is post is very old but here is a function I wrote recently to calculate Business Hours/Minutes between any two events. It also takes into account any holidays which must be defined in a table.

The function returns the interval in minutes - you can divide by 60 to get hours as required.

This has been tested on SQL Server 2008. Hope it helps someone.

Create Function GetWorkingMin(@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int
AS
Begin
    Declare @WorkMin int = 0   -- Initialize counter
    Declare @Reverse bit       -- Flag to hold if direction is reverse
    Declare @StartHour int = 9   -- Start of business hours (can be supplied as an argument if needed)
    Declare @EndHour int = 17    -- End of business hours (can be supplied as an argument if needed)
    Declare @Holidays Table (HDate DateTime)   --  Table variable to hold holidayes

    -- If dates are in reverse order, switch them and set flag
    If @StartDate>@EndDate 
    Begin
        Declare @TempDate DateTime=@StartDate
        Set @StartDate=@EndDate
        Set @EndDate=@TempDate
        Set @Reverse=1
    End
    Else Set @Reverse = 0

    -- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema)
    Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where COUNTRYCODE=@Country and HDATE>=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)

    If DatePart(HH, @StartDate)<@StartHour Set @StartDate = DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate))  -- If Start time is less than start hour, set it to start hour
    If DatePart(HH, @StartDate)>=@EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) -- If Start time is after end hour, set it to start hour of next day
    If DatePart(HH, @EndDate)>=@EndHour+1 Set @EndDate = DateAdd(hour, @EndHour, DateDiff(DAY, 0, @EndDate)) -- If End time is after end hour, set it to end hour
    If DatePart(HH, @EndDate)<@StartHour Set @EndDate = DateAdd(hour, @EndHour-24, DateDiff(DAY, 0, @EndDate)) -- If End time is before start hour, set it to end hour of previous day

    If @StartDate>@EndDate Return 0

    -- If Start and End is on same day
    If DateDiff(Day,@StartDate,@EndDate) <= 0
    Begin
        If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If day is between sunday and saturday
            If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If day is not a holiday
                If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) -- Calculate difference
            Else Return 0
        Else Return 0
    End
    Else Begin
        Declare @Partial int=1   -- Set partial day flag
        While DateDiff(Day,@StartDate,@EndDate) > 0   -- While start and end days are different
        Begin
            If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7    --  If this is a weekday
            Begin
                If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If this is not a holiday
                Begin
                    If @Partial=1  -- If this is the first iteration, calculate partial time
                    Begin 
                        Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
                        Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) 
                        Set @Partial=0 
                    End
                    Else Begin      -- If this is a full day, add full minutes
                        Set @WorkMin=@WorkMin + (@EndHour-@StartHour)*60        
                        Set @StartDate = DATEADD(DD,1,@StartDate)
                    End
                End
                Else Set @StartDate = DATEADD(DD,1,@StartDate)  
            End
            Else Set @StartDate = DATEADD(DD,1,@StartDate)
        End
        If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If last day is a weekday
            If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0   -- And it is not a holiday
                If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
    End 
    If @Reverse=1 Set @WorkMin=-@WorkMin
    Return @WorkMin
End

An alternative solution from @Pavanred's, coming at things from a more data-based angle:

Create a table with all the dates you want to consider in it. For each day, set a number of working hours, like so:

WorkingDate Hours Comment
=========== ===== ==================
 1 Jan 2011     0 Saturday
 2 Jan 2011     0 Sunday
 3 Jan 2011     0 Public Holiday
 4 Jan 2011     8 Normal working day
 5 Jan 2011     8 Normal working day

 -- and so on, for all the days you want to report on.

This will take a small amount of setting up -- you can pre-populate it for weeks versus weekends automatically, then adjust for public holidays, etc, as necessary.

But, what you lose in the setting up, you gain in ease of querying:

SELECT
  SUM(Hours) 
FROM
  working_days 
WHERE
  WorkingDate BETWEEN @StartDate AND @EndDate

...and this can work out as an easier approach if you need to start adding more complicated rules for what defines a working day, or if your working hours vary depending on the day, etc.

It also makes the rules more easily "editable", as you don't need to change any actual code to change the definitions of a working day, add public holidays, etc.