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,442 total views, 1 views today

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