DATEDIFF between two dates, excluding two specific days

Question

In Dubai, the standard weekend runs from Friday - Saturday rather than the traditional Saturday / Sunday.

I am trying to calculate the amount of working days between two given dates, and this is the code I have;

    DATEDIFF(dd, DATEADD(hour, DATEDIFF(hh, getdate(), getutcdate()), @StartDate),
DATEADD(hour, DATEDIFF(hh, getdate(), getutcdate()), @EndDate)+1)
-
(
(DATEDIFF(wk, @StartDate, @EndDate) * 2
+(CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' then 1 else 0 end)
+(CASE WHEN DATENAME(dw, @EndDate) = 'Friday' then 1 else 0 end)
))

)

However it is calculating the wrong amount of days. For example; When the start date is 02-03-2016 and the end date is 02-06-2016 the returns as 4, but it should be 2.

When the start date is 02-03-2016 and the end date is 02-07-2016 the result is 3 which is correct.

You can easily create a calendar table that has, say, one row per day and a column which indicates whether the particular date is a working day. It's easy to write queries against such a table and you can implement whatever rules you want. — Damien_The_Unbeliever

nanestev nanestev · Accepted Answer · 2016-02-11T09:26:29

The code below calculates the working days from your examples correctly. You can wrap it in a scalar function if you want.

declare @from date, @to date;
set @from = '2016-02-03';
set @to = '2016-02-06';
with dates(date)
as
(
 select @from
union all
select dateadd(dd, 1, [date])
from dates
where [date] < @to
)
select count(*) [working days]
from (
select date, case when datename(dw, [date]) in ('Friday', 'Saturday') then 0 else 1 end as working
from dates) as dt
where dt.working = 1

DATEDIFF between two dates, excluding two specific days

1 Answers