Get difference days between two date without specific days

Question

I am trying to write a query which will work out the difference between two dates but without counting specific days.

select ((DATEPART(weekday,(DATEDIFF(DAY,StartDate,EndDate)))) NOT IN (1,5)) FROM MyTable

In this example I'm trying to remove Monday and Friday from difference days (StartDate EndDate)

Perhaps this link? https://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server — Robin Webb, Jul 14 '20 at 09:32
Search for "calendar table". A join to a utility calendar table will allow you to exclude not only weekends, but also other non-business days like holidays. — Dan Guzman, Jul 14 '20 at 09:39

score 0 · Accepted Answer · answered Jul 14 '20 at 09:55

0

Ideally you need calendar table, to do these kinds of calculations easier. Below, I am generating the dates for the range and then counting the days accordingly.

DECLARE @startDate DATE = '2020-07-01'
DECLARE @EndDate DATE = '2020-07-13'

;with AllDates AS
(
    SELECT @StartDate AS DateOf
    UNION ALL
    SELECT DateAdd(day,1,DateOf)
        FROM AllDates
    WHERE DateOf<@EndDate
)
select SUM(CASE WHEN DATENAME(weekday,dateof) in ('Monday','Friday') THEN 0 ELSE 1 END) as SumOfDays
FROM AllDates


+-----------+
| SumOfDays |
+-----------+
|         9 |
+-----------+

answered Jul 14 '20 at 09:55

Venkataraman R

12,181
2
31
58

Thank you so much for your help, but please how I can Integrate into my Query – taii el mehdi Jul 14 '20 at 10:31
@taiielmehdi, you can write a user defined function, which is accepting startDate, EndDate and gives difference. Call the user defined function in your query. – Venkataraman R Jul 14 '20 at 10:38

Get difference days between two date without specific days

1 Answers1