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