Calculating the Number of Business Hours Passed Since a Point of Time
Calculating the number of hours passed since a point of time
This set of user defined functions is used to calculate the number of hours passed since a given date and time excluding the non-working hours . The set contains 8 functions. The function which is called to do the job is [CalcTimeSinceCreated]. This function takes a datetime type value as input. All the other 7 functions are used internally.
This code assumes the working schedule as follows:
- 8 working hours per day from 7.30 AM to 4.00 PM
- Lunch period from 11.30 AM to 12 Noon.
- Saturday & Sunday as off days
The code can be modified to suit any other working hours and days pattern. I used this code to calculate the actual number of working hours passed since a job was started. It can be modified to calculate the number of working hours taken to complete tasks (The function CalcTimeSinceCreated will have to be modified to take two parameters in that case as 'task beginning date&time' and 'task ending date&time', instead of one at present).
Suppose the time right now is November 20, 2 PM and we want to calculate the number of working hours passed since November 16, 3:30 PM. The function will give the result 14.5 hours.
select dbo.CalcTimeSinceCreated('2007-11-16 15:30')
Result = 14.5
The calculation goes as follows:
| Date | Day | Hours | Explanation |
| November 16 | Friday | 0.5 hours | The task started at 3.30 PM and the working hour is upto 4.00 PM |
| November 17 | Saturday | 0 hours | Saturday is an off day |
| November 18 | Sunday | 0 hours | Sunday is a off day |
| November 19 | Monday | 8 hours | 8 full hours in the day |
| November 20 | Tuesday | 6 hours | From 7.30 AM to 2 PM excuding half an hour for the lunch time |
| Total Hours: | 14.5 | |
-- Function1 [DateAtMidnight]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =================================================================================================================
-- Author: <Mazharuddin Ehsan>
-- =================================================================================================================
CREATE function [DateAtMidnight](@DateTime DateTime)
-- Returns the DateTime minus the time portion of the input date; ie the DateTime at midnight
returns datetime
as
begin
return dateadd(dd, datediff(dd,0,@DateTime), 0)
end
-- =================================================================================================================
-- Function 2 [DateAt730]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
CREATE function [DateAt730](@DateTime DateTime)
-- Returns DateTime at 07.30 A.M. for the input date
returns datetime
as
begin
return dateadd(dd, datediff(dd,0,@DateTime),0)+dateadd(ss,(7*3600)+(30*60),0)
end
-- =================================================================================================================
-- Function 3 [DateAt1130]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
CREATE function [DateAt1130](@DateTime DateTime)
-- Returns DateTime at 11.30 AM for the input date
returns datetime
as
begin
return dateadd(dd,datediff(dd,0,@DateTime), 0)+dateadd(ss,(11*3600)+(30*60),0)
end
-- =================================================================================================================
-- Function 4 [DateAt12]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
CREATE function [DateAt12](@DateTime DateTime)
-- Returns DateTime at 12.00 Noon for the input date
returns datetime
as
begin
return dateadd(dd, datediff(dd,0,@DateTime), 0)+dateadd(ss,(12*3600),0)
end
-- =================================================================================================================
-- Function 5 [DateAt16]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
CREATE function [DateAt16](@DateTime DateTime)
-- Returns DateTime at 4.00 P.M. for the input date
returns datetime
as
begin
return dateadd(dd, datediff(dd,0,@DateTime),0)+dateadd(ss,(16*3600),0)
end
-- =================================================================================================================
-- Function 6 [CalcCreateDate]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Mazharuddin Ehsan>
-- =============================================
CREATE function [CalcCreateDate](@DateTime DateTime)
-- Returns the beginning DateTime on a particular office day for calculation
returns datetime
as
begin
if DATEPART(dw, @DateTime) = 7
set @DateTime = dbo.DateAt730(@DateTime + 2)
else
begin
if DATEPART(dw, @DateTime) = 1
set @DateTime = dbo.DateAt730(@DateTime + 1)
end
if @DateTime <= dbo.DateAt730(@DateTime)
set @DateTime = dbo.DateAt730(@DateTime)
else
begin
if @DateTime >= dbo.DateAt1130(@DateTime) and @DateTime <= dbo.DateAt12(@DateTime)
set @DateTime = dbo.DateAt12(@DateTime)
else
if @DateTime >= dbo.DateAt16(@DateTime)
set @DateTime = dbo.DateAt16(@DateTime)
end
return @DateTime
end
-- =================================================================================================================
-- Function 7 [CalcGetDate]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
ALTER function [CalcGetDate]()
-- Returns the ending DateTime on a particular office day for calculations
returns datetime
as
begin
declare @CalcGetDate datetime
set @CalcGetDate = getdate()
if DATEPART(dw, @CalcGetDate) = 7
set @CalcGetDate = dbo.DateAt730(@CalcGetDate + 2)
else
begin
if DATEPART(dw, @CalcGetDate) = 1
set @CalcGetDate = dbo.DateAt730(@CalcGetDate + 1)
end
if @CalcGetDate <= dbo.DateAt730(@CalcGetDate)
set @CalcGetDate = dbo.DateAt730(@CalcGetDate)
else
begin
if @CalcGetDate >= dbo.DateAt1130(@CalcGetDate) and @CalcGetDate <= dbo.DateAt12(@CalcGetDate)
set @CalcGetDate = dbo.DateAt12(@CalcGetDate)
else
if @CalcGetDate >= dbo.DateAt16(@CalcGetDate)
set @CalcGetDate = dbo.DateAt16(@CalcGetDate)
end
return @CalcGetDate
end
-- =================================================================================================================
-- Function 8 [CalcTimeSinceCreated]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Mazharuddin Ehsan>
-- =============================================
CREATE function [CalcTimeSinceCreated](@DateTime DateTime)
-- This is the function that will be called to do the job. All the previous are internally used
-- Returns Total hours passed since the input date. Calculates excluding the off hours (Saturday, Sunday & lunch period (11.30 AM to 12 Noon))
-- Loops through all the days from the input date to the current DateTime to calculate the hours.
returns real
as
begin
declare @CalcHours real
declare @VarCalcCreateDate datetime
if dbo.DateAtMidnight(dbo.CalcCreateDate(@DateTime)) = dbo.DateAtMidnight(dbo.CalcGetDate())
set @CalcHours =
case
when dbo.CalcCreateDate(@DateTime) <= dbo.DateAt1130(dbo.CalcGetDate()) and dbo.CalcGetDate() >= dbo.DateAt1130(dbo.CalcGetDate()) then
round(((cast(datediff(ss, dbo.CalcCreateDate(@DateTime), dbo.CalcGetDate()) as real)-1800)/3600),2)
else
round((cast(datediff(ss, dbo.CalcCreateDate(@DateTime), dbo.CalcGetDate()) as real)/3600),2)
end
else
begin
set @CalcHours =
case
when
dbo.CalcCreateDate(@DateTime) <= dbo.DateAt1130(dbo.CalcCreateDate(@DateTime)) then
round(((cast(datediff(ss, dbo.CalcCreateDate(@DateTime), dbo.DateAt16(dbo.CalcCreateDate(@DateTime))) as real)-1800)/3600),2)
else
round((cast(datediff(ss, dbo.CalcCreateDate(@DateTime), dbo.DateAt16(dbo.CalcCreateDate(@DateTime))) as real)/3600),2)
end
set @VarCalcCreateDate = dbo.CalcCreateDate(@DateTime)
while (1 = 1)
begin
if DATEPART(dw, @VarCalcCreateDate) = 6
set @VarCalcCreateDate = dbo.DateAt730(@VarCalcCreateDate + 3)
else
begin
if DATEPART(dw, @VarCalcCreateDate) = 1
set @VarCalcCreateDate = dbo.DateAt730(@VarCalcCreateDate + 2)
else
set @VarCalcCreateDate = dbo.DateAt730(@VarCalcCreateDate + 1)
end
if dbo.DateAtMidnight(@VarCalcCreateDate) = dbo.DateAtMidnight(dbo.CalcGetDate())
set @CalcHours = @CalcHours + (case
when
@VarCalcCreateDate <= dbo.DateAt1130(dbo.CalcGetDate()) and dbo.CalcGetDate() >= dbo.DateAt1130(dbo.CalcGetDate()) then
round(((cast(datediff(ss, @VarCalcCreateDate, dbo.CalcGetDate()) as real)-1800)/3600),2)
else
round((cast(datediff(ss, @VarCalcCreateDate, dbo.CalcGetDate()) as real)/3600),2)
end)
if dbo.DateAtMidnight(@VarCalcCreateDate) = dbo.DateAtMidnight(dbo.CalcGetDate())
BREAK
else
set @CalcHours = @CalcHours + 8
end
end
return @CalcHours
end 
