Business Time in Seconds Function

,

SQL Function to calculate number of business seconds between 2 datetime fields.
Input Parameters:
@startdate - Start Time (datatype DateTime)
@enddate - End Time (datatype DateTime)
@starthour - Start of Business Day (datatype DateTime) (Format: '1900-01-01 9:00:00')
@endhour - End of Business Day (datatype DateTime) (Format: '1900-01-01 17:00:00')
@workdays - Business Day string - Use Dash ('-') for Non-Business Day (datatype char(7)) (Format: '-MTWTF-')

Note: Function does not exclude holidays.

create function dbo.f_business_seconds (@startdate datetime, @enddate datetime, @starthour datetime, @endhour datetime, @workdays varchar(7))
Returns BigInt as

BEGIN

/****************************************************************************************************************************
* SQL Server Function to determine number of business seconds between two datetime fields.
*
***************************************************************************************************************************** 
* Parameters: 
*	@startdate - Start Time (datatype DateTime)
*	@enddate - End Time (datatype DateTime)
*	@starthour - Start of Business Day (datatype DateTime) (Format: '1900-01-01 9:00:00')
*	@endhour - End of Business Day (datatype DateTime) (Format: '1900-01-01 17:00:00')
*	@workdays - Business Day string - Use Dash ('-') for Non-Business Day (datatype char(7)) (Format: '-MTWTF-')
*
* Example: 
*     dbo.f_business_seconds('2005-01-10 9:00:00','2005-01-16 18:00:00','1900-01-01 9:00:00','1900-01-01 17:00:00','-MTWTF-')
*
* Returns: BigInt value that represents time in seconds
*
*****************************************************************************************************************************/

/****************************************************
* Initialize Variables
*****************************************************/

DECLARE @workstartdate datetime, @workenddate datetime, @workstarttime datetime, @workendtime datetime, @weekendflag bit, @bussecs bigint

set @workstartdate = convert(varchar,@startdate,101)
set @workenddate = convert(varchar,@enddate,101)
set @workstarttime = convert(varchar,@startdate,108)
set @workendtime = convert(varchar,@enddate,108)
set @weekendflag = 0
set @bussecs = 0

/********************************************************************************
* If Start Date is on Non-Workday then rollback start date to last workday
*********************************************************************************/

while substring(@workdays,datepart(dw,@workstartdate),1) = '-'
begin
	set @weekendflag = 1 
	set @workstartdate = dateadd(day,-1,@workstartdate)
end

/*******************************************************************************
* If Start Date is on Non-Workday then set start time to end of last work day
********************************************************************************/

if @weekendflag = 1
begin
	set @workstarttime = @endhour
	set @weekendflag = 0
end	

/******************************************************************************
* If End Date is on Non-Workday then rollback end date to last workday
*******************************************************************************/

while substring(@workdays,datepart(dw,@workenddate),1) = '-'
begin
	set @weekendflag = 1 
	set @workenddate = dateadd(day,-1,@workenddate)
end

/*****************************************************************************
* If End Date is on Non-Workday then set end time to end of last work day
******************************************************************************/

if @weekendflag = 1
begin
	set @workendtime = @endhour
	set @weekendflag = 0
end

/****************************************************************************
* Adjust Start Time and End Time if outside business hours
*****************************************************************************/

if @workstarttime < @starthour
	set @workstarttime = @starthour

if @workendtime < @starthour
	set @workendtime = @starthour

if @workstarttime > @endhour
	set @workstarttime = @endhour

if @workendtime > @endhour
	set @workendtime = @endhour

/**************************************************************************
* Calculate business hours if Start Date and End Date are the same
***************************************************************************/

if @workstartdate = @workenddate
	set @bussecs = datediff(second,(@workstartdate + @workstarttime),(@workenddate  + @workendtime))

/**************************************************************************
* Calculate business hours if Start Date and End Date are the different 
* but no full business days in between.
***************************************************************************/

else
begin
	if @workstartdate = dateadd(day,-1,@workenddate)
		set @bussecs = datediff(second,@workstarttime,@endhour) + datediff(second,@starthour,@workendtime)

/*************************************************************************
* Calculate business hours if Start Date and End Date are the different 
* and have one or more full business days in between.
**************************************************************************/

	else
		begin
			while @workstartdate < @workenddate
			begin	
				set @workstartdate = dateadd(day,1,@workstartdate)

				if substring(@workdays,datepart(dw,@workstartdate),1) <> '-'
					set @bussecs = @bussecs + datediff(second,@starthour,@endhour)
			end
			
			set @bussecs = @bussecs - datediff(second,@starthour,@endhour)
			set @bussecs = @bussecs + datediff(second,@workstarttime,@endhour) + datediff(second,@starthour,@workendtime)

		end
end


/**************************************************
* Return Number of Business Seconds
**************************************************/

return @bussecs
END

Rate

Share

Share

Rate