# Time difference between two dates excluding satarday and sunday

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

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
;
``````

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
FROM DaysList
WHERE Dt < @End
)
SELECT @Ret = Count(Dt)
FROM DaysList
WHERE DatePart( WEEKDAY, Dt) BETWEEN 2 AND 6;

RETURN @Ret;

END
GO``````

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.

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.