T-Sql 2005 Adding hours to a datetime field with the result within working hours

I have two Datetime fields that I wish to add together. They are in the following format: ’01/01/1900 00:00:00′.

The main issue with this is that I want the calculation to only include working hours. The working day is between 08:30 and 17:30 and does not include weekends:

Also if the first field starts out of the working day or is on a weekend then the second field should be added from the start of the next working day.

For example:

`’26/06/2009 15:45:00′ + ’01/01/1900 09:00:00′ = ’29/06/1900 15:45:00′

’12/07/2009 14:22:36′ + ’01/01/1900 18:00:00′ = ’13/07/1900 08:30:00′

’15/07/2009 08:50:00′ + ’01/01/1900 04:00:00′ = ’15/07/2009 12:50:00’`

Im pretty sure that this is going to involve creating a user defined function to work this out but I have no idea how to even start this(I am quite out of my depth here) Could anyone offer me some advice on how to achieve this?

Answer

try this, you might have to put it in a function

DECLARE @Date DATETIME,
        @StartOfDay FLOAT,
        @EndOfDay FLOAT,
        @DateAdd DATETIME

SELECT  @Date ='2009-06-26 15:45:00.000',
        @StartOfDay = 8.5,
        @EndOfDay = 17.5,
        @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 @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date))
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 @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date)) + 1
END

--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)
SET @HoursPerDay = @EndOfDay - @StartOfDay

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

--if we stay in the same day, all is fine
IF (@CurrentHours + @HoursAdd <= @EndOfDay)
BEGIN
    SET @Date = @Date + @DateAdd
END
ELSE
BEGIN
    --remove part of day
    SET @HoursAdd = @HoursAdd - (@EndOfDay - @CurrentHours)
    --,ove to next day
    SET @Date = DATEADD(dd,0, DATEDIFF(dd,0,@Date)) + 1

    --loop day
    WHILE @HoursAdd > 0
    BEGIN
        --add day but keep hours to add same
        IF (DATENAME(dw,@Date) IN ('Saturday','Sunday'))
        BEGIN
            SET @Date = @Date + 1
        END
        ELSE
        BEGIN
            --add a day, and reduce hours to add
            IF (@HoursAdd > @HoursPerDay)
            BEGIN
                SET @Date = @Date + 1
                SET @HoursAdd = @HoursAdd - @HoursPerDay
            END
            ELSE
            BEGIN
                --add the remainder of the day
                SET @Date = DATEADD(mi, (@HoursAdd + @StartOfDay) * 60, DATEDIFF(dd,0,@Date))
                SET @HoursAdd = 0
            END
        END     
    END
END

SELECT @Date

Hope that helps