SQL营业时间计算错误

希望有人可以告诉我这里出错的地方，我开始在Calculate business hours between two dates找到一个营业时间查询，这是第二个答案，因为最佳答案是在测试时没有提供正确的输出 . 我遇到的问题是当我跨越到新的一天并且结束日期时间超过工作日结束时 . 这是我的测试：

选择dbo.BusinessSeconds（'09：00：00'，'17：00：00'，'2014-12-24 16:59:59'，'2014-12-26 18:00:00'）;

因此，如果我从COB之前的1秒到第二天的开始，我得到1秒，这是正确的 . 如果我从同一个开始到第二天的结束，我得到28801，或者1天和1秒 - 这也是正确的 . 但是，如果我将日期结束时间延长到17:00:01到17:59:59它还包括那些秒，但是如果我达到18:00:00，则时间截断到原来的17:00:00结束时天 . 所以我会得到任何给定结束时间的正确数据，除非是在17：00：00-17：59：59的时间范围内，然后我可能会达到我不应该有的额外一小时的时间 .

我将非常感谢任何帮助，因为明天我有一份可交付成果 .

ALTER Function [dbo].[BusinessSeconds](@BusinessDayStart TIME,@BusinessDayEnd TIME,@StartDate DATETIME,@EndDate DATETIME) Returns Int
AS
Begin
--TEST: select dbo.BusinessSeconds('09:00:00','17:00:00','2014-12-24 16:59:59','2014-12-26 18:00:00');
Declare @WorkMin INT = 0   -- Initialize counter
Declare @Reverse BIT       -- Flag to hold if direction is reverse
Declare @StartHour TIME = @BusinessDayStart   -- Start of business hours (can be supplied as an argument if needed)
Declare @EndHour TIME = @BusinessDayEnd    -- 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 DATEADD(month,((LEFT(CAL_ID,4)-1900)*12)+LEFT(RIGHT(CAL_ID,4),2)-1,RIGHT(CAL_ID,2)-1) from B_CALENDAR where CAL_DAY_PK = 263 and HDATE >= DateAdd(dd, DateDiff(dd,0,@StartDate), 0)
Insert Into @Holidays (HDate) Select HolidayDate from V_BUS_HOL Where HolidayDate >= DateAdd(dd, DateDiff(dd,0,@StartDate), 0)

If CAST(@StartDate as TIME) < @StartHour Set @StartDate = CAST(CAST(@StartDate as DATE) as DATETIME) + CAST(@StartHour as DATETIME)  -- If Start time is less than start hour, set it to start hour
If CAST(@StartDate as TIME) >= DATEADD(HOUR,1,@EndHour) Set @StartDate = CAST(CAST(DATEADD(DAY,1,@StartDate) as DATE) as DATETIME) + CAST(@StartHour as DATETIME) -- If Start time is after end hour, set it to start hour of next day
If CAST(@EndDate as TIME) >= DATEADD(HOUR,1,@EndHour) Set @EndDate = CAST(CAST(@EndDate as DATE) as DATETIME) + CAST(@EndHour as DATETIME)  -- If End time is after end hour, set it to end hour
If CAST(@EndDate as TIME) < @StartHour Set @EndDate = CAST(CAST(DATEADD(DAY,-1,@EndDate) as DATE) as DATETIME) + CAST(@EndHour as DATETIME) -- 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(SECOND, @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(SECOND, @StartDate, DateAdd(hour, DATEPART(HOUR,@EndHour), DateDiff(DAY, 0, @StartDate)))
                    Set @StartDate=DateAdd(hour, DATEPART(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 + (DATEPART(HOUR,@EndHour)-DATEPART(HOUR,@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(SECOND, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(SECOND, DateAdd(hour, DATEPART(HOUR,@StartHour), DateDiff(DAY, 0, @StartDate)), @EndDate)
End 
If @Reverse=1 Set @WorkMin=-@WorkMin
Return @WorkMin
End