How to count the number of work days between two dates?

Problem

To count the number of work days between two dates, taking into account weekends (Saturdays and Sundays) and assuming no holidays.

Input

We will create a table named DateRange with two columns: StartDate and EndDate. Here’s an example input:

CREATE TABLE DateRange (
StartDate DATE,
EndDate DATE
);

INSERT INTO DateRange(StartDate, EndDate)
VALUES
('2023-10-01', '2023-10-10'),
('2023-11-01', '2023-11-15'),
('2023-12-01', '2023-12-31');


ExampleTable:

StartDate EndDate
2023-10-01 2023-10-10
2023-11-01 2023-11-15
2023-12-01 2023-12-31

In this table, we have three date ranges. The goal is to calculate the number of work days for each date range.

Desired Solution

StartDate EndDate WorkDays
2023-10-01 2023-10-10 7
2023-11-01 2023-11-15 11
2023-12-01 2023-12-31 22

Solution:

To solve the problem, we need to subtract the weekends between the two dates. Here is the SQL solution to do that:

SELECT
StartDate,
EndDate,
DATEDIFF(EndDate, StartDate) + 1
- ((WEEK(EndDate) - WEEK(StartDate)) * 2)
- CASE WHEN DAYOFWEEK(StartDate) = 1 THEN 1 ELSE 0 END -- Subtract a day if StartDate is a Sunday
- CASE WHEN DAYOFWEEK(StartDate) = 7 THEN 1 ELSE 0 END -- Subtract a day if StartDate is a Saturday
- CASE WHEN DAYOFWEEK(EndDate) = 1 THEN 1 ELSE 0 END   -- Subtract a day if EndDate is a Sunday
- CASE WHEN DAYOFWEEK(EndDate) = 7 THEN 1 ELSE 0 END   -- Subtract a day if EndDate is a Saturday
AS WorkDays
FROM DateRange;


Output:

StartDate EndDate WorkDays
2023-10-01 2023-10-10 7
2023-11-01 2023-11-15 11
2023-12-01 2023-12-31 22

Explanation:

This query first calculates the total days between the two dates using DATEDIFF(). It then subtracts the weekends. The WEEK() function returns the week number of a date, and we use it to determine how many full weekends are between the two dates. The CASE statements at the end are for adjusting the count if the StartDate or EndDate is a weekend day.

Note: This solution assumes there are no holidays between the dates. If there are holidays, they will have to be subtracted from the total as well.

Course Preview