Calculate work hours difference between two date skip holidays and week off in SQL

How to calculate Date difference between working hours from and to date skip holidays and weekly off in SQL?

SQL user defined function to calculate hours difference between “from date” and “to date” parameters by skipping weekly offs (Saturday and Sunday) and holidays.

In this example working hours are considered 12 hours from 7 AM to 7PM and for holidays we used table_holiday. Replace holiday table and conditions as per your requirements.

CREATE FUNCTION getWorkingHoursDiff
(
    @startDate datetime,
    @endDate datetime
)
RETURNS int
AS
BEGIN

    DECLARE @totaldays INT; 
    DECLARE @weekenddays INT;    
    
    DECLARE @ShiftStartOn datetime, @ShiftEndOn datetime
    Declare @startHourDiff int, @endHourDiff int
        
    set @ShiftStartOn = cast(CONVERT(VARCHAR,@startDate,110)+' 07:00:00' as Datetime)
    set @ShiftEndOn = cast(CONVERT(VARCHAR, @endDate,110)+' 19:00:00' as Datetime)
    
    declare @ShiftHours int
    set @ShiftHours = DATEDIFF(HOUR, @ShiftStartOn, @ShiftEndOn)
    
    set @startHourDiff = 0
    
    if @startDate between @ShiftStartOn and DATEADD(hour, @ShiftHours, @ShiftStartOn)
    begin
        set @startHourDiff = DATEDIFF(HOUR, @ShiftStartOn, @startDate)    
        set @startDate = @ShiftStartOn
            
        if(@startHourDiff < 0)    
        begin        
            set @startHourDiff = 0    
        end        
    end
    if DATEPART(WEEKDAY, @startDate) = 1 or DATEPART(WEEKDAY, @startDate) = 7
        set @startHourDiff = 0
        
    if exists(select 1 from table_holiday where DATEDIFF(day, start_time, @startDate) = 0)
        set @startHourDiff = 0    
    
    if @startDate > DATEADD(hour, @ShiftHours, @ShiftStartOn)
        set @startDate = DATEADD(day,1,@ShiftStartOn)    
    
    set @endHourDiff = 0    
    if @endDate between DATEADD(hour, -@ShiftHours, @ShiftEndOn) and @ShiftEndOn
    begin
        set @endHourDiff = DATEDIFF(HOUR, @endDate, @ShiftEndOn)            
        
        set @endDate = @ShiftEndOn
            
        if(@endHourDiff < 0)
            set @endHourDiff = 0    
    end
    if DATEPART(WEEKDAY, @endDate) = 1 or DATEPART(WEEKDAY, @endDate) = 7
        set @endHourDiff = 0
        
    if exists(select 1 from table_holiday where DATEDIFF(day, start_time, @endDate) = 0)
        set @endHourDiff = 0
    
    if @endDate < DATEADD(hour, -@ShiftHours, @ShiftEndOn)
        set @endDate = DATEADD(day,-1,@ShiftEndOn)

    SET @totaldays = DATEDIFF(DAY, @startDate, @endDate) +1     
    
    SET @weekenddays = ((DATEDIFF(WEEK, @startDate, @endDate) * 2) + -- get the number of weekend days in between
                       CASE WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN 1 ELSE 0 END + -- if selection was Sunday, won't add to weekends
                       CASE WHEN DATEPART(WEEKDAY, @endDate) = 7 THEN 1 ELSE 0 END)  -- if selection was Saturday, won't add to weekends

    select @totaldays = @totaldays - @weekenddays
    
    declare @chkdtFromDate datetime, @chkdtToDate datetime
    
    select @chkdtFromDate =  DATEADD(dd, DATEDIFF(dd, 0, @startDate), 0)
    select @chkdtToDate =  DATEADD(dd, DATEDIFF(dd, 0, @endDate), 0)

    select @totaldays = @totaldays - COUNT(1) from table_holiday where (start_time between @chkdtFromDate and @chkdtToDate)
     
    return (@totaldays*@ShiftHours) - @startHourDiff - @endHourDiff
End
Go

3,254 total views, 2 views today