Skip to content
Advertisement

How to get hours between two dates in SQL Server?

I have two dates 2019-01-02 12:33:36.000 and 2019-01-09 19:05:18.000 I want to calculate the hours and mins between the two excluding Saturday and Sunday. I can calculate the difference but not sure how I can exclude Saturday and Sunday from the calculation.

There is no working hours to be excluded just 12AM to 12PM Saturday to Sunday needs to be excluded and I am using SQL Server 2008.

I am using SQL Server 2008

Edit -- From the comments suggestions I have this to calculate weekends
..DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2019-01-02 12:33:36.000'
SET @EndDate = '2019-01-09 19:05:18.000'

SELECT
  (DATEDIFF(wk, @StartDate, @EndDate) * 2)
   +(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'   THEN 1 ELSE 0 END)
   +(CASE WHEN DATENAME(dw, @EndDate)   = 'Saturday' THEN 1 ELSE 0 END) 

Now I will just calculate the mins and Subtract from total.

Advertisement

Answer

So after getting some suggestions from David here is my final code which gets me the mins between two dates excluding weekends

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2019-03-07 00:00:00.000'
SET @EndDate = '2019-03-11 23:59:59.000'

Declare @TotalMins int
Declare @Weekends int
Declare @FinalMinutes int

Set @TotalMins =  DATEDIFF(MINUTE, @StartDate,@EndDate);

Set @Weekends = 
  (DATEDIFF(wk, @StartDate, @EndDate) * 2)
   +(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'   THEN 1 ELSE 0 END)
   +(CASE WHEN DATENAME(dw, @EndDate)   = 'Saturday' THEN 1 ELSE 0 END)

Set @FinalMinutes = @TotalMins -  (@Weekends * 24 * 60)
Select @FinalMinutes

Thanks

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement