SQL Server – Calculating Business Days Between Two Dates (With Holiday Exclusion)

Note: The post is the same as the previous one for Oracle but this is for SQL Server. Although the date arithmetic gets a little complicated by the SQL Server syntax, the concept is the same.

If you search the web on how to calculate the number of business days between two dates in SQL Server, you would see so many results. Here is one more with some explanation to go with it.

Example requirement

Let us say that this is the business requirement – “Today, I am calculating the number for days between two dates to get the response time in days but I would really like to exclude the weekends to get an accurate picture”.

I am going to simulate this business scenario below where it shows an activity’s start and end date

WITH response_time
AS
(
    SELECT
            GETDATE() - 600 AS start_date,
            GETDATE() - 500 AS end_date
)
SELECT
    a.*,
    DATEDIFF(day, a.start_date, a.end_date) AS reponse_time_in_days
FROM response_time a

The results are simple enough:

START_DATE	END_DATE	REPONSE_TIME_IN_DAYS
7/1/2012 15:15	10/9/2012 15:15	100

Excluding “weekends”

Now, how do we take out the “weekend days” out from the “RESPONSE_TIME_IN_DAYS”?

• First, we need to know all the dates in between the start and end dates.
• Then, we need to determine which dates among those are either a Saturday or a Sunday
• Finally, we count the resulting dates

Let us go about doing this. Initially, we are going to use a brute force approach and generate all “days” after 1/1/1950 for 100 years (roughly till Dec, 2050) assuming our start and end dates will fall within that range (we will refine the solution later). This article at SQLPerformance.com shows how to generate n rows dynamically and also explains why “MAXDOP 1” was used.

SELECT
	DATEADD(day,
		(seq-1),
		CONVERT(datetime, '1950/01/01 00:00:00', 120)
		) AS day_date,
	DATEPART(dw,
		DATEADD(day,
			(seq-1),
			CONVERT(datetime, '1950/01/01 00:00:00', 120)
			)
		) AS day_of_week
FROM
(
	--100 years
	SELECT TOP (365 * 100) seq = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
	FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) AS a
ORDER BY 1
OPTION (MAXDOP 1)

This query is literally going to generate 100 years’ worth of dates, one per day from 1/1/1950

DAY_DATE	DAY_OF_WEEK
1-Jan-1950	1
2-Jan-1950	2
3-Jan-1950	3
4-Jan-1950	4
5-Jan-1950	5
6-Jan-1950	6
7-Jan-1950	7
…..more rows inbetween
3-Dec-2049	6
4-Dec-2049	7
5-Dec-2049	1
6-Dec-2049	2

Day 1 and 7 make the weekend

In the table above, DAY_OF_WEEK 7 and 1 correspond to Saturday and Sunday respectively (whose corresponding dates, we want to exclude). More specifically, the numbering is as follows:

Day of Week	Day
1	Sunday
2	Monday
3	Tuesday
4	Wednesday
5	Thursday
6	Friday
7	Saturday

Exclude the weekends

Below, I have put the above pieces together

WITH days
AS
(
	SELECT
		DATEADD(day,
			(seq-1),
			CONVERT(datetime, '1950/01/01 00:00:00', 120)
			) AS day_date,
		DATEPART(dw,
			DATEADD(day,
				(seq-1),
				CONVERT(datetime, '1950/01/01 00:00:00', 120)
				)
			) AS day_of_week
	FROM
	(
		--100 years
		SELECT TOP (365 * 100) seq = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
		FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
	) AS a
),
response_time
AS
(
    SELECT
            GETDATE() - 600 AS start_date,
            GETDATE() - 500 AS end_date
)
SELECT
    MAX(DATEDIFF(day, start_date, end_date)) AS days_inbetween,
    COUNT(1) business_days_inbetween
FROM
    response_time resp,
    days
WHERE
    ---------------------------------------------------
    --Get only the dates between our start and end dates
    ---------------------------------------------------
    days.day_date >= resp.start_date
    AND days.day_date <= resp.end_date
    ---------------------------------------------------
    --...then exclude Sat and Sun
    ---------------------------------------------------
    AND days.day_of_week NOT IN (7,1)
OPTION (MAXDOP 1)

And the results are as follows:

DAYS_INBETWEEN	BUSINESS_DAYS_INBETWEEN
100	72

Exclude holidays too, please!

That was easy enough. Different countries and regions have different holidays. It is not a good idea to hard-code the list of holidays in your query. Now, if we had a calendar table which had the holidays, just add that in to the end of the query (note the highlighted part). The Holidays_Table could have just three columns Region, HolidayDate which is a date and a Description to describe the holiday.

WHERE
    ---------------------------------------------------
    --Get only the dates between our start and end dates
    ---------------------------------------------------
    days.day_date >= resp.start_date
    AND days.day_date <= resp.end_date
    ---------------------------------------------------
    --...then exclude Sat and Sun
    ---------------------------------------------------
    AND days.day_of_week NOT IN (7,1)
    ---------------------------------------------------
    --...then exclude week-day holidays
    ---------------------------------------------------
    AND NOT EXISTS
        (
            SELECT holiday
            FROM holidays_table ht
            WHERE ht.holiday = days.day_date
--Optionally include Region!
        )

Need a more efficient solution?

While this works, it can certainly be tweaked a bit more. We do not need to generate 100 years’ worth of dates – we need to generate exactly the dates in between the start date and end date.

WITH days
AS
(
      SELECT
            DATEADD(day, (seq-1), @Begin_Date) AS day_date,
            DATEPART(dw, DATEADD(day, (seq-1), @Begin_Date)) day_of_week
      FROM
      (
            --Only the x days between begin and end dates
            SELECT TOP (@Days_Between) seq
                              = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
            FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
      ) a
)
      ….rest of the SQL

Where the variables could be (for example)

DECLARE @Begin_Date DATETIME
DECLARE @End_Date DATETIME
DECLARE @Days_Between INT

SET @Begin_Date = CONVERT(datetime, '1971/01/01 00:00:00', 120)
SET @End_Date  = CONVERT(datetime, '1981/01/01 00:00:00', 120)
SET @Days_Between = DATEDIFF(day, @Begin_Date, @End_Date)

Notice the highlighted variable substitutions and you will see that we have actually made the day generator SQL simpler and more efficient too.