How to calculate difference between two dates in seconds during business hours for business days SQL Server?

Question

I like to get the difference in seconds between two working days in SQL Server. We have date table contains IsWorkingDay flag, if the end date is null then it should default to getdate(). The workday starts at 8 am and finishes at 4:30 pm.

I have the following query, need help in else part.

@StartDate and @EndDate will not always be on workdays. If @StartDate is on any weekend or Holiday, it should roll up to Next working day at 8:00 am. If @EndDate is on any weekend or Holiday, it should roll up to last working day at 4:30 pm.

CREATE FUNCTION TimeDiffInSeconds
(
    @Startdate DATETIME
    ,@EndDate DATETIME
)
RETURNS INT 
AS 
BEGIN
    DECLARE @WorkSeconds INT = 0;
    DECLARE @Reverse BIT;
    DECLARE @StartHour FLOAT = 8
    DECLARE @EndHour FLOAT = 16.50

    IF @Startdate > @EndDate
        BEGIN
            DECLARE @TempDate DATETIME = @Startdate;
            SET @Startdate = @EndDate;
            SET @EndDate = @TempDate;
            SET @Reverse = 1;
        END;
    ELSE 
        SET @Reverse = 0;


    IF DATEPART(HH, @StartDate) < @StartHour
        SET @StartDate = DATEADD(HOUR, @StartHour, DATEDIFF(DAY, 0, @StartDate)); 
    IF DATEPART(HH, @StartDate) >= @EndHour + 1
        SET @StartDate = DATEADD(HOUR, @StartHour + 24, DATEDIFF(DAY, 0, @StartDate));
    IF DATEPART(HH, @EndDate) >= @EndHour + 1
        SET @EndDate = DATEADD(HOUR, @EndHour, DATEDIFF(DAY, 0, @EndDate)); 
    IF DATEPART(HH, @EndDate) < @StartHour
        SET @EndDate = DATEADD(HOUR, @EndHour - 24, DATEDIFF(DAY, 0, @EndDate)); 

    IF @Startdate > @EndDate
    RETURN 0;


    IF DATEDIFF(DAY, @StartDate, @EndDate) <= 0
        BEGIN
            IF @Startdate <> (SELECT date_id FROM Final.Date WHERE IsWorkingDay = 0)
                SET @WorkSeconds = DATEDIFF(ss, @StartDate, @EndDate); -- Calculate difference
            ELSE RETURN 0;
        END;
    ELSE 

    --need help




RETURN @WorkSeconds;
END 

Answer 1

Below function will provide seconds for shift time and excluding weekend and holidays:

---

CREATE FUNCTION [dbo].[TimeDiffInSeconds] 
(
    @Startdate datetime,
    @EndDate datetime
)         
RETURNS numeric (30)
AS
BEGIN 

    DECLARE @StartdateNew DATETIME, @EndDateNew DATETIME, @Seconds numeric(30) = 0
 
    -- considering shift time 8AM to 5PM
    SET @StartdateNew =  CONVERT(DATETIME, CONVERT(CHAR(8), @Startdate+1, 112) + ' ' + CONVERT(CHAR(8), '08:00:00:000', 108))
    SET @EndDateNew =  CONVERT(DATETIME, CONVERT(CHAR(8), @EndDate-1, 112) + ' ' + CONVERT(CHAR(8), '17:00:00:000', 108))

    IF (CONVERT(TIME, @Startdate) < '08:00:00:000')
    BEGIN 
        SET @Startdate = (CONVERT(DATETIME, CONVERT(CHAR(8), @Startdate, 112) + ' ' + CONVERT(CHAR(8), '08:00:00:000', 108)))
    END
    IF (CONVERT(TIME, @EndDate) > '17:00:00:000')
    BEGIN 
        SET @EndDate = (CONVERT(DATETIME, CONVERT(CHAR(8), @EndDate, 112) + ' ' + CONVERT(CHAR(8), '17:00:00:000', 108)))
    END

    IF (@Startdate > @EndDate)
    BEGIN
        SET @EndDate = @Startdate
    END

    -- seconds for whole days excluding first and last day
    IF (@EndDateNew > @StartdateNew)
    begin
        declare @TotalWorkDays int, @SaturdayCount int,  @SundayCout int,  @HolidayCout int
        select @TotalWorkDays = DATEDIFF(DAY, @StartdateNew, @EndDateNew) + 1

        SELECT @SaturdayCount = count(1) FROM (SELECT TOP ( datediff(DAY,@StartdateNew,@EndDateNew) + 1) [Date] = dateadd(DAY,ROW_NUMBER() OVER(ORDER BY c1.name), DATEADD(DD,-1,@StartdateNew))
        FROM   [master].[dbo].[spt_values] c1 ) x WHERE  datepart(dw,[Date]) = 7;

        SELECT @SundayCout = count(1) FROM (SELECT TOP ( datediff(DAY,@StartdateNew,@EndDateNew) + 1) [Date] = dateadd(DAY,ROW_NUMBER() OVER(ORDER BY c1.name), DATEADD(DD,-1,@StartdateNew))
        FROM   [master].[dbo].[spt_values] c1 ) x WHERE  datepart(dw,[Date]) = 1;
    
        SELECT @HolidayCout = count(1) 
        FROM   [DBO].[HolidayMaster] c1 WHERE  STATUS = 'ACTIVE' AND DATENAME(WEEKDAY, [DATE]) NOT IN ('Saturday','Sunday')
            and [DATE] between CAST(@StartdateNew as date) and CAST(@EndDateNew as date);

        set @Seconds = (@TotalWorkDays - (@SaturdayCount+@SundayCout+@HolidayCout) ) * 32400
    end

    -- For Same day seconds
    IF (CONVERT(CHAR(8), @Startdate, 112) = CONVERT(CHAR(8), @EndDate, 112)
        AND CONVERT(TIME, @Startdate) >= '08:00:00:000' AND CONVERT(TIME, @EndDate) <= '17:00:00:000'
        AND DATENAME(WEEKDAY, @Startdate) NOT IN ('Saturday','Sunday') 
        AND CAST(@Startdate AS DATE) NOT IN (SELECT [DATE] FROM [DBO].[HolidayMaster] WHERE STATUS = 'ACTIVE'))
    BEGIN
        SET @Seconds = @Seconds + DateDiff(second,@Startdate,@EndDate)
    end

    -- For First day seconds
    if (CONVERT(CHAR(8), @Startdate, 112) <> CONVERT(CHAR(8), @EndDate, 112) 
        AND CONVERT(TIME, @Startdate) <= '17:00:00:000' 
        AND DATENAME(WEEKDAY, @Startdate) NOT IN ('Saturday','Sunday')
        AND CAST(@Startdate AS DATE) NOT IN (SELECT [DATE] FROM [DBO].[HolidayMaster] WHERE STATUS = 'ACTIVE'))
    begin
        SET @Seconds = @Seconds + DateDiff(second,@Startdate,(CONVERT(DATETIME, CONVERT(CHAR(8), @Startdate, 112) + ' ' + CONVERT(CHAR(8), '17:00:00:000', 108))))
    end

    -- For Last Day Seconds
    if (CONVERT(CHAR(8), @Startdate, 112) <> CONVERT(CHAR(8), @EndDate, 112)
        AND CONVERT(TIME, @EndDate) >= '08:00:00:000' 
        AND DATENAME(WEEKDAY, @EndDate) NOT IN ('Saturday','Sunday') 
        AND CAST(@EndDate AS DATE) NOT IN (SELECT [DATE] FROM [DBO].[HolidayMaster] WHERE STATUS = 'ACTIVE'))
    begin
        SET @Seconds = @Seconds + DateDiff(second,(CONVERT(DATETIME, CONVERT(CHAR(8), @EndDate, 112) + ' ' + CONVERT(CHAR(8), '08:00:00:000', 108))),@EndDate)
    end

    return @Seconds

END

Answer 2

It can be calculated in a simple way.

If your two parameters are already working days then you can just return their difference in seconds, minus 15.5 hours (16:30 to 08:00) for every day difference (every time the midnight boundary is crossed), minus 8,5 hours (8:00 to 16:30) for every not working day between your two dates.

PS: I have updated the answer, so we now first check if @StartDate and @EndDate are correct working datetimes, and if not then we move them to the correct ones. After that you can apply the previously described calculation for the working time in seconds.

CREATE FUNCTION TimeDiffInSeconds
(
    @Startdate datetime,
    @EndDate datetime
)
RETURNS INT 
AS 
BEGIN   
  set @EndDate = coalesce(@EndDate, getdate());

  -- We check that @StartDate is a working datetime, and if not we set it to the next one      

  if convert(time, @StartDate) < convert(time, '08:00') 
  begin
    set @StartDate = convert(datetime, convert(date, @StartDate)) + 
                     convert(datetime, '08:00')
  end   
  if convert(time, @StartDate) > convert(time, '16:30') or
     (select IsWorkingDay 
      from Final.Date 
      where date_id = convert(date, @StartDate)) = 0 
  begin
    select top 1 @StartDate = convert(datetime, date_id) + 
                              convert(datetime, '08:00')
    from Final.Date
    where date_id > @StartDate and IsWorkingDay = 1  
    order by date_id 
  end     

  -- We check that @EndDate is a working datetime, and if not we set it to the last one      

  if convert(time, @EndDate) > convert(time, '16:30') 
  begin
    set @EndDate = convert(datetime, convert(date, @EndDate)) + 
                   convert(datetime, '16:30')
  end   
  if convert(time, @EndDate) < convert(time, '08:00') or
     (select IsWorkingDay 
      from Final.Date 
      where date_id = convert(date, @EndDate)) = 0 
  begin
    select top 1 @EndDate = convert(datetime, date_id) + 
                            convert(datetime, '16:30')
    from Final.Date
    where date_id < @EndDate and IsWorkingDay = 1 
    order by date_id desc   
  end     

  -- We return the working time difference in seconds between @StartDate and @EndDate

  RETURN datediff(second, @StartDate, @EndDate) -    
     ((15.5 * datediff(day, @StartDate, @EndDate) * 60 * 60) -
      (8.5 * (select count(*) 
              from Final.Date 
              where IsWorkingDay = 0 and
                   (date_id between @StartDate and @EndDate or date_id between @EndDate and @StartDate)
             ) * 60 * 60));
END

Test online: https://rextester.com/FWR14059