How to Count Week Days Between Two Dates? – Interview Question of the Week #132

Interview question: How many weekdays fall between two dates in SQL Server?

Answer: First decide whether both endpoints count and what “weekday” means. For an inclusive Monday-through-Friday count, generate the dates in the interval and test each against a fixed Monday. That avoids a session’s DATEFIRST setting and translated weekday names. It doesn’t subtract public holidays.

Some interview questions are simple enough that writing the SQL is fun. Compact formulas look clever, but the boundary conditions matter more than the cleverness.

For July 1 through July 31, 2017, inclusive, this query counts each date and checks whether it is Monday through Friday:

DECLARE @FirstDate  date = '20170701';
DECLARE @SecondDate date = '20170731';
IF @FirstDate > @SecondDate
    THROW 50000, 'First date must be on or before second date.', 1;
WITH CalendarDate AS
(
    SELECT @FirstDate AS work_date
    UNION ALL
    SELECT DATEADD(day, 1, work_date)
    FROM CalendarDate
    WHERE work_date < @SecondDate
)
SELECT COUNT(*) AS weekday_count
FROM CalendarDate
WHERE ((DATEDIFF(day, CONVERT(date, '19000101', 112), work_date)
          % 7) + 7) % 7 < 5
OPTION (MAXRECURSION 0);

The result for that month is 21. January 1, 1900 was a Monday; the normalized remainder gives Monday through Friday values below five, even for dates before the anchor. Both endpoints are included, because the date generator starts at the first date and stops after producing the second.

Shortcuts That Break

Two shortcuts show up often. One uses master..spt_values and compares its numbers with day-of-year values. That system table is undocumented for this purpose, day-of-year boundaries don’t describe elapsed days across years, and the trick assumes one DATEFIRST setting. The other compares English DATENAME values such as “Sunday”; its answer changes when the session language changes. Test any formula with intervals that cross weekends, years and language settings.

For a large or frequently queried date range, use a proper calendar table. It can also store your organization’s holidays and working-day rules. The recursive query above is a small self-contained interview demonstration, not a calendar dimension replacement.

Counting weekdays is not a formula puzzle, it is a definition question, so settle the endpoints and the holidays first.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.