none
Calculate Elapsed Time Between Dates Excluding Weekends RRS feed

  • 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;
    
    --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
    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
    Moderador
  • 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
    Moderador

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
    Moderador
  • 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
    Moderador
  • 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
    Moderador
  • 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
    Moderador
  • 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
    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
    Moderador
  • 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
    Moderador
  • 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