Calculating the number of weekdays between two dates

I wish the DATEDIFF() function would count the number of working days (mondays through fridays) between two dates for me, but until that happens, I’ve had to roll my own scalar function. I tried to think of a smart way involving perhaps a modulus calculation, but I quickly succumbed to a more down-to-earth approach.

In essence, the calculation happens in three steps; calculate the number of full (not partial) weeks between the two dates, multiply that by five, add the number of weekdays in the starting week, and finally add the number of weekdays in the finishing week.

This would all be pretty simple if DATEFIRST was always predictably 1, meaning that weeks always started on mondays.

CREATE FUNCTION dbo.fn_WeekdayCount(
    @fromDate   date,
    @toDate     date
)
RETURNS int
AS

BEGIN;
    DECLARE @WeekdayCount int;

    SELECT @WeekdayCount=x.FullWeeks*5+
                         x.DaysBefore+
                         x.DaysAfter
    FROM (
        VALUES (
            --- How many full weeks have elapsed? This number will be -1
            --- if the from and to dates are in the same week.
            DATEDIFF(week,
                     DATEADD(day, 8-DATEPART(dw, @FromDate), @FromDate),
                     DATEADD(day, 1-DATEPART(dw, @ToDate), @ToDate)),
            --- How many weekdays remain in the starting week?
            (CASE WHEN DATEPART(dw, @FromDate)>=6 THEN 0
                  ELSE 6-DATEPART(dw, @FromDate) END),
            --- How many weekdays are used in the ending week?
            (CASE WHEN DATEPART(dw, @ToDate)>=6 THEN 5
                  ELSE DATEPART(dw, @ToDate) END))
        ) AS x(FullWeeks, DaysBefore, DaysAfter);

    RETURN @WeekdayCount;
END;

Note that this function will return the number of days between @FromDate and @ToDate inclusive, meaning that from monday to friday is five days, not the usual four you would usually get with DATEDIFF. You can probably fix that easily enough if you need to.

Using @@DATEFIRST

But what if you live in a strange and distant land somewhere on earth, where weeks for some reason start on sundays? Since we can’t use SET DATEFIRST inside scalar functions (or maybe you just can’t use SET DATEFIRST in your existing procedures or queries for other reasons), the solution will have to use @@DATEFIRST and offset the calculation accordingly.

CREATE FUNCTION dbo.fn_WeekdayCount(
    @fromDate   date,
    @toDate     date
)
RETURNS int
AS

BEGIN;
    DECLARE @WeekdayCount int;

    SELECT @WeekdayCount=x.FullWeeks*5+
                         x.DaysBefore+
                         x.DaysAfter
    FROM (
        VALUES (
            --- How many full weeks have elapsed? This number will be -1
            --- if the from and to dates are in the same week.
            DATEDIFF(week,
                     DATEADD(day, 7-(@@DATEFIRST-1+DATEPART(dw, @FromDate))%7, @FromDate),
                     DATEADD(day, 0-(@@DATEFIRST-1+DATEPART(dw, @ToDate))%7, @ToDate)),
            --- How many weekdays remain in the starting week?
            (CASE (@@DATEFIRST-1+DATEPART(dw, @FromDate))%7
                  WHEN 0 THEN 5
                  WHEN 6 THEN 0
                  ELSE 6-(@@DATEFIRST-1+DATEPART(dw, @FromDate))%7 END),
            --- How many weekdays are used in the ending week?
            (CASE (@@DATEFIRST-1+DATEPART(dw, @ToDate))%7
                  WHEN 6 THEN 5
                  ELSE (@@DATEFIRST-1+DATEPART(dw, @ToDate))%7 END))
        ) AS x(FullWeeks, DaysBefore, DaysAfter);

    RETURN @WeekdayCount;
END;

Eliminating the scalar function

It has to be said, if performance is important (and it should be), you’ll know that user-defined scalar T-SQL functions can kill your query. So you might want to implement the above in the form of a CROSS APPLY instead:

SELECT
     -- your select columns go here..
       x.FullWeeks*5+x.DaysBefore+x.DaysAfter AS WeekdayCount
FROM something AS a 
     -- your tables and joins go here
     -- aaaaand...

CROSS APPLY (
        VALUES (
            --- How many full weeks have elapsed? This number will be -1
            --- if the from and to dates are in the same week.
            DATEDIFF(week,
                     DATEADD(day, 7-(@@DATEFIRST-1+DATEPART(dw, a.FromDate))%7, a.FromDate),
                     DATEADD(day, 0-(@@DATEFIRST-1+DATEPART(dw, a.ToDate))%7, a.ToDate)),
            --- How many weekdays remain in the starting week?
            (CASE (@@DATEFIRST-1+DATEPART(dw, a.FromDate))%7
                  WHEN 0 THEN 5
                  WHEN 6 THEN 0
                  ELSE 6-(@@DATEFIRST-1+DATEPART(dw, a.FromDate))%7 END),
            --- How many weekdays are used in the ending week?
            (CASE (@@DATEFIRST-1+DATEPART(dw, a.ToDate))%7
                  WHEN 6 THEN 5
                  ELSE (@@DATEFIRST-1+DATEPART(dw, a.ToDate))%7 END))
        ) AS x(FullWeeks, DaysBefore, DaysAfter);

Whether it’s US or ISO week numbers or weeks starting on mondays/sundays, week calculations can throw you for a loop if you’re not paying attention – particularly if you use one setting in your development environment and another in production.

Watch out for those @@DATEFIRST settings.

6 comments

  1. Just for fun, i tryed another aproach, without “fancy” date calculations
    It has a decrease performance with longer than 20 years interval

    but this CTE aproach allow one to even list those day instead of counting them

    CREATE FUNCTION dbo.fn_WeekdayCount_CTE_way(
    @fromDate date,
    @toDate date
    )
    RETURNS int
    AS

    BEGIN;
    DECLARE @WeekdayCount int;
    WITH DATAS AS (
    SELECT @fromDate AS data
    , DATEPART(dw, @fromDate) AS dw
    UNION ALL
    SELECT DATEADD(DAY, 1, D.Data) AS data
    , DATEPART(dw, DATEADD(DAY, 1, D.Data)) AS dw
    FROM DATAS D
    WHERE D.data < @toDate
    )
    SELECT @WeekdayCount = COUNT(data)
    FROM DATAS
    WHERE dw between 2 and 6
    OPTION (MAXRECURSION 0);

    RETURN @WeekdayCount;
    END;

    Reply

  2. I used a calendar table for a similar issue, but my requirement was slightly more complicated: I also had to count holidays. We needed to know 3 days out from start date, but a weekend or holiday would push that further out. I added fixed date holidays for 30 years before I left that job, but the manager needs to add the variable date holidays or the system will drift.

    Reply

    1. Yep, calendar tables are pretty much ubiquitous. What I was trying to solve was really the performance aspect – when, for instance, you have a million date ranges to process, you don’t really want to make that many seeks or scans on a calendar table.

      But I suppose you could use my solution above combined with a LEFT JOIN to a “holidays” table that just contains the actual holidays, thus keeping the table size down considerably.

      Reply

    1. Thanks for your feedback! I realize that I didn’t really google for any existing solutions to this problem before writing my post. 🙂

      I haven’t read through all of the answers, but a common denominator for those I did look at seems to be that they all assume a certain @@DATEFIRST and/or language setting, which can be a deal-breaker in many applications.

      My main focus and biggest challenge was creating a solution that
      * uses a single SQL statement, so as to avoid having to using a UDF,
      * does not require @@DATEFIRST to be set,
      * does not require any particular SET language.

      Reply

      1. That answer is easily adjusted to eliminate all the culture/locale settings. Since datediff() always uses Saturday to Sunday as the boundary for weeks, nothing needs to change except in the portion that checks for specific days of the week on the start and end. I always thought it was one of the more elegant solutions I’ve come across and I’m surprised that nobody had posted a more versatile version yet.

        select
        datediff(day, , ) – datediff(week, , ) * 2
        – case when datepart(weekday, ) = 8 – @@datefirst then 1 else 0 end /* start is Sunday */
        – case when datepart(weekday, ) = (13 – @@datefirst) % 7 + 1 then 1 else 0 end /* end is Saturday */

        Reply

Leave a comment

Your email address will not be published. Required fields are marked *