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