SQL-Server I’m getting crazy with workday calculation with datepart

I’m working on SQL-Server 2012 and have the following code example to get workdays between two dates

DECLARE @StartDate AS date
DECLARE @EndDate AS date

SET @StartDate = '2019/02/18' -- this is a monday
SET @EndDate = '2019/02/23'  -- this is a saturday

SELECT 
       DATEDIFF(DD, @StartDate, @EndDate) 
       - (DATEDIFF(WK, @StartDate,@EndDate) * 2) 
       - CASE WHEN DATEPART(DW, @StartDate) = 1 THEN 1 ELSE 0 END 
       - CASE WHEN DATEPART(DW, @EndDate) = 1 THEN 1 ELSE 0 END

the result is 4, which is correct…

But If I put 2019/02/24 (sunday) for the EndDate I’m getting 3… ????

I’m getting crazy here…

Answer

You’re validating your Enddate to be Sunday instead of Saturday. I had a similar function available that is independent of date settings.

SELECT ISNULL((((DATEDIFF(dd,@StartDate,@EndDate)) --Start with total number of days including weekends
              - (DATEDIFF(wk,@StartDate,@EndDate)*2) --Subtact 2 days for each full weekend
              - (1-SIGN(DATEDIFF(dd,6,@StartDate)%7)) --If StartDate is a Sunday, Subtract 1
              - (1-SIGN(DATEDIFF(dd,5,@EndDate)  %7)))) , 0)  --If StartDate is a Saturday, Subtract 1
    WHERE @StartDate <= @EndDate