Calculate Elapsed Time Between Dates Excluding Weekends

# Calculate Elapsed Time Between Dates Excluding Weekends

• Thursday, February 25, 2010 4:24 PM

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

### All Replies

• Thursday, February 25, 2010 4:37 PM
Moderator

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
• Thursday, February 25, 2010 4:45 PM
Moderator

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
• Thursday, February 25, 2010 4:50 PM
Moderator

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

• Thursday, February 25, 2010 5:01 PM
Moderator

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
• Thursday, February 25, 2010 5:28 PM

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;

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'

--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
UNION	ALL
FROM	cte
)
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
• Thursday, February 25, 2010 6:05 PM
Moderator

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
• Thursday, February 25, 2010 6:11 PM
Moderator

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