Time difference between two dates excluding satarday and sunday

1

how to calculate a time difference between two dates excluding satarday and sunday in sql and date should not be null

2

This is one way of doing it:

select from_date
      ,to_date
	  ,(datediff(day,from_date,to_date)+1)/7*5
	  +case when (datediff(day,from_date,to_date)+1)%7>=1 and datepart(weekday,to_date) not in (1,7) then 1 else 0 end
	  +case when (datediff(day,from_date,to_date)+1)%7>=2 and datepart(weekday,dateadd(day,-1,to_date)) not in (1,7) then 1 else 0 end
	  +case when (datediff(day,from_date,to_date)+1)%7>=3 and datepart(weekday,dateadd(day,-2,to_date)) not in (1,7) then 1 else 0 end
	  +case when (datediff(day,from_date,to_date)+1)%7>=4 and datepart(weekday,dateadd(day,-3,to_date)) not in (1,7) then 1 else 0 end
	  +case when (datediff(day,from_date,to_date)+1)%7>=5 and datepart(weekday,dateadd(day,-4,to_date)) not in (1,7) then 1 else 0 end
	  +case when (datediff(day,from_date,to_date)+1)%7>=6 and datepart(weekday,dateadd(day,-5,to_date)) not in (1,7) then 1 else 0 end
       as d
  from yourtable
;
3

Here's another way:

USE Jic
GO

IF OBJECT_ID (N'dbo.unfWeekDaysBetween') IS NOT NULL
   DROP FUNCTION dbo.unfWeekDaysBetween
GO
/* SELECT dbo.unfWeekDaysBetween('2016-08-01', '2016-08-22') WeekDaysBetween
*/
CREATE FUNCTION dbo.unfWeekDaysBetween (@Beg date, @End date)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
   
   DECLARE @Ret int;
      
   WITH DaysList (Dt)
   AS
      (
      SELECT @Beg
      UNION ALL
      SELECT DateAdd(dd, 1, Dt) 
      FROM DaysList
      WHERE Dt < @End
      )
   SELECT @Ret = Count(Dt)
   FROM DaysList
   WHERE DatePart( WEEKDAY, Dt) BETWEEN 2 AND 6;

   RETURN @Ret;

END
GO
4

No... I wouldn't do it that way. It uses an rCTE (Recursive CTE) that "increments" and it uses a scalar function. Both are pretty bad for performance. Please see the following articles for proof of that.

http://www.sqlservercentral.com/articles/T-SQL/74118/
http://www.sqlservercentral.com/articles/T-SQL/74118/

5

If all you want to really know is the number of week days between two dates, the following will do it for you. Depending on the language of your server, you may have to change the names of the weekend days, but the arithmetic is very simple and very fast.

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(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)
;

For an explanation of how it works (it's based on the "boundary" of a week which is why we need no condition for if the @EndDate occurs on a Sunday, for example), please see the following article.
http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/