T-SQL 2005：带午休、节假日与额外工时的Datetime字段工作时长累加函数异常排查

Let's break down the problems in your modified ufn_getworkhr function and walk through fixes step by step.

Problem Overview #

You've extended an existing function by Adriaan Stander to account for lunch breaks, holidays (marked as EXTRA=0 in tbl_workhrs), and extra workdays (EXTRA=1), but the function returns incorrect results. Let's start by examining the code and key flaws.

Modified Function Code #

alter function [dbo].ufn_getworkhr(@Date DATETIME, @DateAdd DATETIME) returns datetime AS 
BEGIN 
    DECLARE @StartOfDay FLOAT = 8.5 ; 
    DECLARE @EndOfDay FLOAT = 17.5 ; 
    DECLARE @StartOfLunch FLOAT = 12.5; 
    DECLARE @EndOfLunch FLOAT = 13.5; 
    DECLARE @Datefinal DATETIME 
    DECLARE @StartDay DATETIME 
    DECLARE @ActStartDay DATETIME 
    DECLARE @EndDay DATETIME 
    DECLARE @ActEndDay DATETIME 
    DECLARE @cntmin int 

    -- DECLARE @Date DATETIME = '2021-10-06 08:01:00.000'; 
    -- DECLARE @DateAdd DATETIME = '1900-01-02 03:01:00.000'; 
    -- set @DateAdd = '1900-01-01 09:00:00.000'; 

    --fix up start date 
    --before start of day, move to start of day 
    IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) < @StartOfDay) 
    BEGIN 
        SET @ActStartDay=@Date 
        -- print 'before start of day, move to start of day' 
        SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date)) 
        SET @StartDay=@Date 
        --SELECT DATEDIFF(MINUTE, @ActStartDay,@StartDay) 
        SET @cntmin=DATEDIFF(MINUTE, @ActStartDay,@StartDay) 
    END 

    --after close of day, move to start of next day 
    IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) > @EndOfDay) 
    BEGIN 
        SET @ActEndDay=@Date 
        -- print 'after close of day, move to start of next day' 
        SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date)) + 1 
        SET @EndDay=@Date 
        --SELECT @Date,DATEDIFF(MINUTE,@EndDay, @ActEndDay) 
    END 

    DECLARE @DATA_START1 DATETIME, @DATA_END1 DATETIME,@EXTRA1 INT 
    DECLARE db_cursor1 CURSOR FOR SELECT DATA_START , DATA_END,EXTRA FROM tbl_workhrs 
    OPEN db_cursor1 
    FETCH NEXT FROM db_cursor1 INTO @DATA_START1 , @DATA_END1 ,@EXTRA1 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        IF @EXTRA1=0 
        BEGIN 
            IF DATEDIFF(DD,@DATE, @DATA_START1)=0 
            BEGIN 
                SET @Date = @Date + 1 
            END 
        END 
        FETCH NEXT FROM db_cursor1 INTO @DATA_START1 , @DATA_END1 ,@EXTRA1 
    END 
    CLOSE db_cursor1 
    DEALLOCATE db_cursor1 

    --move to monday if on weekend 
    WHILE DATENAME(dw, @Date) IN ('Saturday','Sunday') 
    BEGIN 
        SET @Date = @Date + 1 
    END 

    --get the number of hours to add and the total hours per day 
    DECLARE @HoursPerDay FLOAT 
    DECLARE @HoursAdd FLOAT 
    SET @HoursAdd = DATEDIFF(hh, '1900-01-01 00:00:00.000', @DateAdd) 
    DECLARE @HoursAddmins FLOAT 
    SET @HoursAddmins=DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, @DateAdd), 0), @DateAdd) 
    SET @HoursPerDay = @EndOfDay - @StartOfDay 

    --date the time of geiven day 
    -- select @date 
    DECLARE @CurrentHours FLOAT 
    SET @CurrentHours = CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24 
    DECLARE @CurrentHoursmin FLOAT 
    SET @CurrentHoursmin=DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0), @Date) 

    --if we stay in the same day, all is fine 
    --IF (@CurrentHours + @HoursAdd <= @EndOfDay) 
    --select @date 
    IF (@CurrentHoursmin + @HoursAddmins <= ((@EndOfDay*60)-60)) 
    BEGIN 
        -- print 'if we stay in the same day, all is fine' 
        -- SET @Date = @Date + @DateAdd 
        SET @Date= DATEADD(mi,@HoursAddmins, @Date); 
    END 
    ELSE 
    BEGIN 
        -- print 'remove part of day' 
        -- SET @HoursAdd = @HoursAdd - (@EndOfDay - @CurrentHours) 
        SET @HoursAddmins = @HoursAddmins - (((@EndOfDay*60) - @CurrentHoursmin)-60) 
        --select @HoursAddmins 

        -- move to next day 
        SET @Date = DATEADD(dd,0, DATEDIFF(dd,0,@Date)) + 1 
        --select @date 

        -- loop day 
        --WHILE @HoursAdd > 0 
        WHILE @HoursAddmins > 0 
        BEGIN 
            -- add day but keep hours to add same 
            IF (DATENAME(dw,@Date) IN ('Saturday','Sunday')) 
            BEGIN 
                -- select 123,@date 
                IF EXISTS(SELECT 1 FROM tbl_workhrs WHERE @DATE BETWEEN DATA_START AND DATA_END AND EXTRA=1) 
                BEGIN 
                    --select 321 
                    ---SELECT @DATE 
                    SET @Date = DATEADD(mi, (@HoursAddmins+(@StartOfDay*60))-60, DATEDIFF(dd,0,@Date)) 
                    SET @HoursAddmins = 0 
                END 
                ELSE 
                BEGIN 
                    --SELECT 1233 
                    ---- print '--add day but keep hours to add same' 
                    SET @Date = @Date + 1 
                END 
                --SET @Date = @Date + 1 
            END 
            ELSE 
            BEGIN 
                -- select 321 
                DECLARE @DATA_START DATETIME, @DATA_END DATETIME,@EXTRA INT 
                -- add a day, and reduce hours to add 
                --IF (@HoursAdd > @HoursPerDay) 
                DECLARE db_cursor CURSOR FOR SELECT DATA_START , DATA_END,EXTRA FROM tbl_workhrs 
                OPEN db_cursor 
                FETCH NEXT FROM db_cursor INTO @DATA_START , @DATA_END ,@EXTRA 
                WHILE @@FETCH_STATUS = 0 
                BEGIN 
                    IF @EXTRA=0 
                    BEGIN 
                        IF @DATE BETWEEN @DATA_START AND @DATA_END 
                        BEGIN 
                            --SELECT @DATE,1 
                            SET @Date = @Date + 1 
                            --SELECT @DATE,2 
                        END 
                    END 
                    FETCH NEXT FROM db_cursor INTO @DATA_START , @DATA_END ,@EXTRA 
                END 
                CLOSE db_cursor 
                DEALLOCATE db_cursor 

                IF (@HoursAddmins > (@HoursPerDay*60)) 
                BEGIN 
                    -- select 22,@Date 
                    -- print '@HoursAdd > @HoursPerDay' 
                    SET @Date = @Date + 1 
                    --SET @HoursAdd = @HoursAdd - @HoursPerDay 
                    SET @HoursAddmins = @HoursAddmins - (@HoursPerDay*60) 
                    -- SET @HoursAddmins =@HoursAddmins -(24*60) 
                END 
                ELSE 
                BEGIN 
                    -- select 11 
                    -- print 'add the remainder of the day' 
                    --SET @Date = DATEADD(mi, (@HoursAdd + @StartOfDay) * 60, DATEDIFF(dd,0,@Date)) 
                    --SET @HoursAdd = 0 
                    SET @Date = DATEADD(mi, @HoursAddmins+(@StartOfDay*60), DATEDIFF(dd,0,@Date)) 
                    SET @HoursAddmins = 0 
                END 
                -- print @DAte 
            END 
        END 
    END 

    -- print 'dsdf' 
    -- SELECT @date,1234 
    IF DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0), @Date) > (@StartOfLunch*60) 
    BEGIN 
        IF DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0), @Date) < (@EndOfLunch*60) 
        BEGIN 
            SET @Date= DATEADD(mi, (DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0), @Date) - (@StartOfLunch*60))+59, @Date); 
        END 
        ELSE 
        BEGIN 
            SET @Date= DATEADD(mi,((@EndOfLunch-@StartOfLunch)*60)+60, @Date); 
        END 
    END 

    --ELSE 
    --BEGIN 
    ----SELECT 13 
    -- IF EXISTS(SELECT 1 FROM tbl_workhrs WHERE @DATE BETWEEN DATA_START AND DATA_END AND EXTRA=0) 
    -- BEGIN 
    -- SET @Date= DATEADD(mi,@HoursAddmins+60, @Date); 
    -- END 
    --END 

    IF DATEPART(DAY, @DateAdd)>1 
    BEGIN 
        SET @Date=DATEADD(DAY, DATEPART(DAY, @DateAdd)-1, @Date) 
    END 

    SET @Datefinal = (SELECT @Date) 
    --print @Datefinal 
    RETURN @Datefinal 
    -- select * from tbl_workhrs 
END

Key Issues & Fixes #

1. Holiday Handling Logic is Incomplete #

Your first cursor (db_cursor1) only checks if @Date equals the start date of a holiday (DATA_START1), but holidays might span multiple days. This means you're skipping only the first day of multi-day holidays, not the entire period.

Fix:
Replace the cursor logic with a loop that checks if @Date falls within any holiday range (EXTRA=0), and increments @Date until it's not a holiday:

-- Replace the db_cursor1 block with this
WHILE EXISTS(SELECT 1 FROM tbl_workhrs WHERE @Date BETWEEN DATA_START AND DATA_END AND EXTRA=0)
BEGIN
    SET @Date = DATEADD(DAY, 1, @Date)
    -- Also re-check weekends after moving past a holiday
    WHILE DATENAME(dw, @Date) IN ('Saturday','Sunday')
    BEGIN
        SET @Date = DATEADD(DAY, 1, @Date)
    END
END

2. Lunch Break Adjustment has Math Errors #

The current lunch adjustment logic uses inconsistent minute calculations:

• When the final time is during lunch, you add (current minutes - start lunch minutes) +59 which adds an extra hour instead of skipping the lunch period.
• When it's after lunch, you add (1 hour) +60 which adds 2 hours total, which is wrong.

Fix:
Adjust the lunch logic to skip the entire 1-hour lunch break correctly:

-- Replace the lunch adjustment block with this
DECLARE @CurrentTimeMinutes INT = DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0), @Date)
DECLARE @LunchStartMinutes INT = @StartOfLunch * 60
DECLARE @LunchEndMinutes INT = @EndOfLunch * 60

IF @CurrentTimeMinutes > @LunchStartMinutes
BEGIN
    IF @CurrentTimeMinutes < @LunchEndMinutes
    BEGIN
        -- If during lunch, jump to end of lunch
        SET @Date = DATEADD(MINUTE, @LunchEndMinutes - @CurrentTimeMinutes, @Date)
    END
    ELSE
    BEGIN
        -- If after lunch, add the lunch duration (since we didn't account for it in work hours)
        SET @Date = DATEADD(MINUTE, @LunchEndMinutes - @LunchStartMinutes, @Date)
    END
END

3. Work Hour Calculation Uses Floating-Point Risks #

Casting datetime values to FLOAT can introduce precision errors. Use integer minute calculations instead for accuracy.

Fix:
Replace all floating-point hour calculations with integer minutes:

-- Replace start/end day checks with minute-based logic
DECLARE @StartOfDayMinutes INT = 8 * 60 + 30 -- 8:30 AM
DECLARE @EndOfDayMinutes INT = 17 * 60 + 30 -- 5:30 PM
DECLARE @CurrentTimeMinutes INT = DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0), @Date)

IF @CurrentTimeMinutes < @StartOfDayMinutes
BEGIN
    SET @cntmin = @StartOfDayMinutes - @CurrentTimeMinutes
    SET @Date = DATEADD(MINUTE, @cntmin, @Date)
END
ELSE IF @CurrentTimeMinutes > @EndOfDayMinutes
BEGIN
    SET @Date = DATEADD(DAY, 1, DATEADD(MINUTE, @StartOfDayMinutes, DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0)))
END

4. Extra Workdays (Weekends) Logic Flaw #

When handling weekends with EXTRA=1, you're setting @HoursAddmins = 0 immediately, which doesn't account for cases where the extra workday can't fit all remaining hours.

Fix:
Treat extra workdays like regular workdays: subtract the workday minutes from @HoursAddmins and continue looping if needed:

-- Replace the weekend EXTRA=1 block with this
IF EXISTS(SELECT 1 FROM tbl_workhrs WHERE @DATE BETWEEN DATA_START AND DATA_END AND EXTRA=1)
BEGIN
    DECLARE @WorkDayMinutes INT = @EndOfDayMinutes - @StartOfDayMinutes
    IF @HoursAddmins > @WorkDayMinutes
    BEGIN
        SET @HoursAddmins = @HoursAddmins - @WorkDayMinutes
        SET @Date = DATEADD(DAY, 1, @Date)
    END
    ELSE
    BEGIN
        SET @Date = DATEADD(MINUTE, @StartOfDayMinutes + @HoursAddmins, DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0))
        SET @HoursAddmins = 0
    END
END

5. Final @DateAdd Day Handling is Incorrect #

The line IF DATEPART(DAY, @DateAdd)>1 BEGIN SET @Date=DATEADD(DAY, DATEPART(DAY, @DateAdd)-1, @Date) END adds extra days based on the day part of @DateAdd, which is not aligned with the work hour calculation logic. This is likely a leftover debug line that should be removed, since you already accounted for the duration in @HoursAddmins.

Fix:
Delete that entire block.

Additional Best Practices #

• Avoid cursors where possible: Use set-based operations to check holidays instead of cursors for better performance.
• Add test cases: Test with edge cases like:
  • Start time before work hours
  • Start time during lunch
  • Adding hours that span multiple holidays/weekends
  • Extra workdays on weekends
• Use DATEPART(WEEKDAY, @Date) instead of DATENAME(dw, @Date) for locale-independent weekend checks (adjust the numbers based on your server's SET DATEFIRST setting).

内容的提问来源于stack exchange，提问作者Pedro Rodrigues