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

Question

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 1

you could use the dayofweek function and some in-line case statements;

http://www.smallsql.de/doc/sql-functions/date-time/dayofweek.html

http://www.tizag.com/sqlTutorial/sqlcase.php

so, you'd perform the calculation if the dayofweek function didn't return sat. or sun.; else return a null.

I think you could get away without writing a user-defined function, but the sql statement would look a bit messy. but then again most non-basic sql statements all look a bit messy!

Answer 2

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