2

I need to find hours between 2 dates excluding weekends. How to do it in MS SQL Server?

I have tried this,

CAST(( GETDATE() - [approval_date]) AS FLOAT) * 24.0 AS WorkedHours,

its including weekends.

Suhaib Janjua
  • 3,538
  • 16
  • 59
  • 73
Somashekhar
  • 493
  • 1
  • 12
  • 33
  • possible duplicate of http://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server – Ameya Deshpande Mar 16 '15 at 04:45
  • possible duplicate of [Count work days between two dates](http://stackoverflow.com/questions/252519/count-work-days-between-two-dates) – Noel Mar 16 '15 at 04:49
  • 1
    Before posting the question I have tried all, Those answers give days, If I multiply with 24 Hours, will get Days*24, I am not getting exact Hours, in one day it may be 10 Hours also – Somashekhar Mar 16 '15 at 05:09

4 Answers4

2

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 Saturday(s):

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 
    WHEN  DATEDIFF(WK, @d1, @d2) = 0 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  
    WHEN  DATEDIFF(WK, @d1, @d2) = 0 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
    WHEN  DATEDIFF(WK, @d1, @d2) = 0 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 
                                WHEN  DATEDIFF(WK, @DateFrom, @DateTO) = 0 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  
                    WHEN  DATEDIFF(WK, @DateFrom, @DateTO) = 0 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  
                                WHEN  DATEDIFF(WK, @DateFrom, @DateTO) = 0 THEN 0 
                            ELSE 1 END
                            )
        END
        RETURN @result 
END
Sohail Arshed
  • 21
  • 4
  • Just to let you know, I tried running that function with the following parameters and it does not return the expected result. Expected 12, actual 1 DateFrom DATETIME ='2020-03-06 12:00:00' DateTO DATETIME = '2020-03-08 13:00:00' Type VARCHAR(50) = 'Hour' – jorge cordero Mar 07 '20 at 03:27
2

Unfortunately this was not as straight forward as I thought it was going to be.

I took this blog/tutorial as a base and started working from there.

This is the scalar function I am now using. I did some smoke testing and it seems to work just fine. If you see any errors please let me know so I can test and update the solutio as necessary. I hope this helps others!

ALTER FUNCTION [dbo].[GetWeekDayHoursDiff](@DateFrom datetime, @DateTo datetime)
RETURNS INT 

AS
BEGIN 

IF @DateFrom > @DateTo  
BEGIN
    return CAST('DateFrom cannot be lower thant DateTo parameters' as int);  -- https://www.youtube.com/watch?v=2ZIpFytCSVc
END 

DECLARE @TotHours INT= DATEDIFF(Hour, @DateFrom, @DateTo);
DECLARE @TotWeekHours INT= DATEDIFF(WEEK, @DateFrom, @DateTo) * 48;
DECLARE @SundayOffset INT= CASE
                     WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN  24 - DATEPART(HOUR, @DateFrom)
                     WHEN DATENAME(WEEKDAY, @DateFrom) = 'Saturday' THEN - (DATEPART(HOUR, @DateFrom))
                     ELSE 0
                  END;
DECLARE @SaturdayOffset INT= CASE
                       WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN - DATEPART(HOUR, @DateTo)
                       WHEN DATENAME(WEEKDAY, @DateTo) = 'Sunday' THEN  - DATEPART(HOUR, @DateTo) + 24
                       ELSE 0
                    END;


DECLARE @TotWorkingHours INT= @TotHours - @TotWeekHours - @SundayOffset + @SaturdayOffset;

   -- select @TotWorkingHours TotWorkingHours
      --   , @TotHours TotHours
         --, @TotWeekHours TotWWeekHours
         --, @SundayOffset SundayOffset
         --, @SaturdayOffset SaturdayOffset

RETURN @TotWorkingHours


END
jorge cordero
  • 91
  • 1
  • 3
  • This is the way. I tried some of the other solutions I found across SO and all failed with edge cases. This was the only one that passed all the tests I threw at it. – Steve Platz Dec 09 '22 at 12:14
-2

Try this

select datediff(hh, @d1, @d2)

where @d1 = date1 and @d2 = date2

In your case it would be like this

 select datediff(hh, GETDATE(), [approval_date])
SimarjeetSingh Panghlia
  • 2,200
  • 13
  • 14
-2

Based on get DATEDIFF excluding weekends using sql server, follow this query to get the hours

SELECT DATEDIFF(HH, @date1, @date2) - (DATEDIFF(WK, @date1, @date2) * 2) -
       CASE WHEN datepart(DW, @d1) = 1 THEN 24 ELSE 0 END +
       CASE WHEN datepart(DW, @d2) = 1 THEN 24 ELSE 0 END
Community
  • 1
  • 1
sqluser
  • 5,502
  • 7
  • 36
  • 50