Calculate Elapsed Time Between Dates Excluding Weekends

Question

Could anyone tell me how to calculate the elapsed time (minutes) between two datetime values, but exclude the weekend?

Answer 1

Here is one solution (this returns hours, easy to convert hours to minutes):

http://pratchev.blogspot.com/2008/01/calculating-work-hours.html

---

Plamen Ratchev

Answer 2

Plamen,

Your function simply assumes 8 hours working day. It doesn't calculate actual working hours based on the datetime.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 3

You could use a calendar table. Pull all dates between the start and end date, count whole days and multiply it by (24 * 60), and add the difference in min for the start date related to 00:00 next day, and add the difference in minute for the end date related to 00:00 same date.

declare @x int;
declare @sd datetime, @ed datetime;

select @sd = '2010-01-01T12:35:00', @ed = '2010-01-03T17:15:00';

select @x = count(*) * (24 * 60) as c1
from calendar
where dt >= dateadd([day], datediff([day], 0, @sd) + 1, 0) and dt < dateadd([day], datediff([day], 0, @ed), 0);

set @x = @x + datediff([minute], @sd, dateadd([day], datediff([day], 0, @sd) + 1, 0));
set @x = @x + datediff([minute], dateadd([day], datediff([day], 0, @ed), 0), @ed);
GO


AMB

Answer 4

That is easy to adjust but without knowing the exact requirements it is not worth to guess. In most cases it is considered the full work day.

---

Plamen Ratchev

Answer 5

Could anyone tell me how to calculate the elapsed time (minutes) between two datetime values, but exclude the weekend?

I wouldn't swear by it but give this a go! Have to go now so don't have time to test!

declare	@startTime				datetime
,		@endTime				datetime
,		@IntervalIncWeekends	int;

--ADD YOUR DATE VALUES TO TEST HERE 
set		@startTime = '20100225 23:58'
set		@endTime = '20100227 12:34'

--If start date is on a weekend, convert to midnight between sunday and monday
IF		DATENAME(dw, @startTime) = 'Saturday'
		SET	@startTime = CAST(CAST(DATEADD(dd,2,@startTime) AS DATE) AS DATETIME); -- convert to start of Monday
IF		DATENAME(dw, @startTime) = 'Sunday'
		SET	@startTime = CAST(CAST(DATEADD(dd,1,@startTime) AS DATE) AS DATETIME); -- convert to start of Monday

--If end date is on a weekend, convert to 1 minute before the end of friday
IF		DATENAME(DW, @endTime) = 'Saturday'
		SET	@endTime = DATEADD(MI,-1, CAST(CAST(@endTime AS DATE) AS DATETIME));
IF		DATENAME(DW, @endTime) = 'Sunday'
		SET	@endTime = DATEADD(MI,-1, CAST(CAST(DATEADD(dd,-1,@endTime) AS DATE) AS DATETIME));

--If @endTime < @startTime then they both occur on the same weekend, thus zero mins between them
IF		@endTime <= @startTime
BEGIN
		--select	'@startTime=' + convert(varchar(25),@startTime,120),'@endTime=' + convert(varchar(25),@endTime, 120);
		SELECT	0;
END
ELSE
BEGIN
		DECLARE	@NumberofWeekendDays	INT = 0;
		;WITH cte as (
				SELECT	CAST(DATEADD(DD,1,@startTime) AS DATE) AS dt
				WHERE	DATEADD(DD,1,@startTime) < @endTime
				UNION	ALL
				SELECT	DATEADD(DD,1,dt)
				FROM	cte
				WHERE	DATEADD(DD,1,dt) < @endTime
		)
		SELECT	@NumberofWeekendDays = COUNT(*)
		FROM	cte
		WHERE	DATENAME(dw, dt) IN ('Saturday','Sunday');
		--select	'@startTime=' + CAST(@startTime as varchar(25)),'@endTime=' + CAST(@endTime as varchar(25)),'@NumberofWeekendDays=' + CAST(@NumberofWeekendDays as varchar(10));
		SELECT	DATEDIFF(MI,@startTime,@endTime) - (@NumberofWeekendDays * 60 * 24);
END

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 6

That is easy to adjust but without knowing the exact requirements it is not worth to guess. In most cases it is considered the full work day.

---

Plamen Ratchev

Agreed.

We do not have to provide the exact answer / solution to the question / problem. Providing the idea is as important as providing the exact answer. It is up to the OP to expand and develop the idea.


AMB

Answer 7

I agree - I just wanted for OP to clarify the requirements and post the table structure. What if he logs the Employee timein/timeout (including the lunch breaks) ?

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog