Calculate the number of days between two dates not including weekend days

This one’s not as tricky as it seems. Initially, for those not wanting a detailed explanation of how this works, i’d like to show the equation and the SQL, then go into the explanation.

The equation:

(EndDate - StartDate)
-
(
  (Number of Weeks between StartDate and EndDate x2)
  +1 [if StartDate is a Sunday]
  +1 [if EndDate is a Saturday]
)

The SQL:

DECLARE @StartDate datetime, @EndDate datetimeSELECT @StartDate='2011-01-02', @EndDate='2011-01-13'

SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1) --Number of days between Start and End
-
(
(DATEDIFF(wk, @StartDate, @EndDate) * 2) --Weekend Days
+(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) --Taking into consideration the startdate being a Sunday
+(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) --Taking into consideration the enddate being a Saturday
)

Explanation

For this whole example i have selected a date range which begins on a Sunday, ends on a Thursday and includes 3 weekend days. In order to get the number of days (24 hour periods) between two date ranges you’d usually use DATEDIFF:

DECLARE @StartDate datetime, @EndDate datetime

SELECT @StartDate='2011-01-02', @EndDate='2011-01-13'

SELECT DATEDIFF(d,@startdate,@enddate)

Result: 11

Explanation: 11x 24 hour periods between 2011-01-02 00:00:00.000 AND 2011-01-13 00:00:00.000

So if we wanted to know how many days rather than 24 hour periods the date range includes within it we would need to +1 that number. It might seem like you should calculate the time of the date to make sure, but you don’t – you can simple +1 the days. To prove this, in the example above the widest we can make the date range is to extend the @EndDate to ’2011-01-13 23:59:59.997′ (or 9 if you prefer!) so we can never include 12 days. Likewise with regards to the time element, the above example is the most limited we can get. I just wanted to clarify the +1 rule actually worked always, and as far as i can figure it does.

So 12 ‘days’ between the two dates, or 11x 24 hour periods going on the way DATEDIFF works. How many Saturday’s and Sundays?

Looking at the calendar we know there are 3 weekend days. So our total needs to show 12-3=9 days (not including the weekend days). Once we get this figure i’ll test it against a few different date ranges. So far we know we can DATEDIFF+1, now we need to subtract the number of weekend days. We know that each week contains two weekend days – Saturday and Sunday. So we can account for the days between the start and end date by calculating the number of weeks between the two dates and multiplying that number by 2, again using DATEDIFF:

DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='2011-01-02', @EndDate='2011-01-13'

SELECT DATEDIFF(wk, @StartDate, @EndDate) * 2 --Weekend Days

That calculates the number of 7 day periods between the two dates. So 13th – 2nd = 11 days. 11 days / 7 days (rounded down) = 1

So that now shows we have one period of 7 days between the two days (which we multiply by 2) = 2 days.

We know that there’s actually 3 days between the start and end dates (if you include the start and end dates). We need to take into account that if we define a start or end date as a weekend day then it should add these days onto the total to be deducted. A case statement resolves this, which concludes our statement:

DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='2011-01-02', @EndDate='2011-01-13'

 SELECT
 (DATEDIFF(dd, @StartDate, @EndDate) + 1) --Number of days between Start and End
 -
 (
  (DATEDIFF(wk, @StartDate, @EndDate) * 2) --Weekend Days
  +(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) --Taking into consideration the startdate being a Sunday
  +(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) --Taking into consideration the enddate being a Saturday
  )

I’ve tested it out in a few different scenarios and it seems to work ok.

Performance!

So you have a table which has a StartDate and EndDate column. You have a load of rows, and you want to perform this calculation out on all the rows. You’ll need an algorithm that performs well! I’m not going to include the SQL to generate test data to test this against, as I simply don’t have time. But i’m going to run this algorithm against a table here and show you how it performs and see if we can gain any improvements from it.

The table i’m running this against has around 10,000 rows in. I’m going to use the DateCreated and DateModified columns in this particular table to test the performance of this algorithm. I’m not going to put the algorithm in a function for now, as i want to see the raw performance. I may put it into a function later if there’s negligible performance difference in doing so.

I’ve just executed it against that data-set using the DateCreated as the StartDate and DateModified as the end date and it’s executed in a split second! (There are no indexes against the date columns in case you wondered).

I created a function to house this algorithm and the performance of that function was very similar. But i’m open to ideas if anyone can see a way to increase the performance!

Here’s the function for anyone interested (sorry about the name!)

CREATE FUNCTION dbo.DATEDIFF_WorkingDaysOnly(@StartDate datetime, @EndDate datetime)
RETURNS INT
AS
BEGIN
 RETURN (DATEDIFF(dd, @StartDate, @EndDate) + 1) --Number of days between Start and End
 -
 (
  (DATEDIFF(wk, @StartDate, @EndDate) * 2) --Weekend Days
  +(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) --Taking into consideration the startdate being a Sunday
  +(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) --Taking into consideration the enddate being a Saturday
  )
END