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…
Advertisement
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