# Calculate Elapsed Time Between Dates Excluding Weekends • ### Pergunta

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

quinta-feira, 25 de fevereiro de 2010 16:24

### Respostas

• 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
quinta-feira, 25 de fevereiro de 2010 17:28
• 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
quinta-feira, 25 de fevereiro de 2010 16:37
• 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

quinta-feira, 25 de fevereiro de 2010 16:50

### Todas as Respostas

• 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
quinta-feira, 25 de fevereiro de 2010 16:37
• 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
quinta-feira, 25 de fevereiro de 2010 16:45
• 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

quinta-feira, 25 de fevereiro de 2010 16:50
• 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
quinta-feira, 25 de fevereiro de 2010 17:01
• 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
quinta-feira, 25 de fevereiro de 2010 17:28
• 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
quinta-feira, 25 de fevereiro de 2010 18:05
• 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
quinta-feira, 25 de fevereiro de 2010 18:11
• This is an old post but it might help someone. This query returns the exact days, months and hours between two dates excluded All Sunday(s) and Monday(s):
```
declare @d1 datetime, @d2 datetime
select @d1 = '11/25/2019 12:00:00',  @d2 = '12/02/2019 12:00:00'

SELECT  (DATEDIFF(MINUTE, @d1, @d2) -  DATEDIFF(WK, @d1, @d2) * 2880) + CASE WHEN  DATEDIFF(WK, @d1, @d2) = 1 AND DATEDIFF(DW, @d1, @d2) <= 5 THEN 0 ELSE 1440 END

SELECT (DATEDIFF(HOUR, @d1, @d2) - DATEDIFF(WK, @d1, @d2) * 48) + CASE WHEN  DATEDIFF(WK, @d1, @d2) = 1 AND DATEDIFF(DW, @d1, @d2) <= 5 THEN 0  ELSE 24 END

SELECT (DATEDIFF(DW, @d1, @d2) - DATEDIFF(WK, @d1, @d2) * 2) + CASE WHEN  DATEDIFF(WK, @d1, @d2) = 1 AND DATEDIFF(DW, @d1, @d2) <= 5 THEN 0  ELSE 1 END
```

Or you can use this generic function:

```
CREATE FUNCTION [dbo].[fn_GetBusinnessDaysTimeSpan](
@DateFrom DATETIME,
@DateTO DATETIME,
@Type VARCHAR(50)

RETURNS BIGINT
AS
BEGIN
DECLARE @result AS BIGINT;
IF @Type = 'Min'
BEGIN
SET @result = (SELECT  (DATEDIFF(MINUTE, @DateFrom, @DateTO) -  DATEDIFF(WK, @DateFrom, @DateTO) * 2880) + CASE WHEN  DATEDIFF(WK, @DateFrom, @DateTO) = 1 AND (DATEDIFF(DW, @DateFrom, @DateTO)) <= 5 THEN 0 ELSE 1440 END)
END

IF @Type = 'Hour'
BEGIN
SET @result = (SELECT (DATEDIFF(HOUR, @DateFrom, @DateTO) - DATEDIFF(WK, @DateFrom, @DateTO) * 48) + CASE WHEN  DATEDIFF(WK, @DateFrom, @DateTO) = 1 AND (DATEDIFF(DW, @DateFrom, @DateTO)) <=5 THEN 0  ELSE 24 END)
END

IF @Type = 'Day'
BEGIN
SET @result = (SELECT (DATEDIFF(DW, @DateFrom, @DateTO) - DATEDIFF(WK, @DateFrom, @DateTO) * 2) + CASE WHEN  DATEDIFF(WK, @DateFrom, @DateTO) = 1 AND (DATEDIFF(DW, @DateFrom, @DateTO)) <=5 THEN 0  ELSE 1 END)
END
RETURN @result
END
```
quarta-feira, 27 de novembro de 2019 09:47