Get number of weekdays (Sundays, Mondays, Tuesdays) between two dates SQL

Question

So, I'd like to, for a Start Date and End Date, determine how many particular days of the week occur between these two dates.

So how many mondays, tuesdays, etc

I know I can do this with a loop between the Start and End Date and check each day, but the difference could possibly be a great number of days. I'd prefer something that didn't require a loop. Any ideas? (Must be supported in SQL Server 2005+)

If you also need to discount national holidays, company holidays, etc. then I strongly recommend a calendar table. http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html — Aaron Bertrand, Jul 13 '11 at 19:57
I agree with @Aaron. This would also preserve a company-wide definition of 'start-of-week', and pevent it from being changed arbitrarily in the database. — Clockwork-Muse, Jul 13 '11 at 20:05

score 9 · Accepted Answer · answered Jul 13 '11 at 19:55

9

Given what I think you're trying to get, this should do it:

SET DATEFIRST 1

DECLARE
    @start_date DATETIME,
    @end_date DATETIME

SET @start_date = '2011-07-11'
SET @end_date = '2011-07-22'

;WITH Days_Of_The_Week AS (
    SELECT 1 AS day_number, 'Monday' AS day_name UNION ALL
    SELECT 2 AS day_number, 'Tuesday' AS day_name UNION ALL
    SELECT 3 AS day_number, 'Wednesday' AS day_name UNION ALL
    SELECT 4 AS day_number, 'Thursday' AS day_name UNION ALL
    SELECT 5 AS day_number, 'Friday' AS day_name UNION ALL
    SELECT 6 AS day_number, 'Saturday' AS day_name UNION ALL
    SELECT 7 AS day_number, 'Sunday' AS day_name
)
SELECT
    day_name,
    1 + DATEDIFF(wk, @start_date, @end_date) -
        CASE WHEN DATEPART(weekday, @start_date) > day_number THEN 1 ELSE 0 END -
        CASE WHEN DATEPART(weekday, @end_date)   < day_number THEN 1 ELSE 0 END
FROM
    Days_Of_The_Week

answered Jul 13 '11 at 19:55

Tom H

46,766
14
87
128

Testing this now, seems to be what i'm looking for. – Steph Jul 13 '11 at 20:36
Would work perfectly... except it's in a user defined function... and being using for a computed column... can't set datefirst :-( – Steph Jul 13 '11 at 20:54
SET DATEFIRST is only necessary if you *need* to set it because your system doesn't use the standard/default (which is 1). – Aaron Bertrand Jul 13 '11 at 21:11
I thought the standard/default was 7? – Steph Jul 13 '11 at 21:12
1

Oh whoops, yes I suppose that is right. Well you can always overcome this by just shifting what your day_numbers are. – Aaron Bertrand Jul 13 '11 at 21:36

KM. · Answer 2 · 2011-07-13T19:52:24.733

I'm not sure what the OP is after, this will give a count per weeks day:

SET DATEFIRST 1
DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate='7/13/2011'
      ,@EndDate='7/28/2011'
;with AllDates AS
(
    SELECT @StartDate AS DateOf, datename(weekday,@StartDate) AS WeekDayName, datepart(weekday,@StartDate) AS WeekDayNumber
    UNION ALL
    SELECT DateOf+1, datename(weekday,DateOf+1), datepart(weekday,DateOf+1)
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT COUNT(*) CountOf,WeekDayName  FROM AllDates GROUP BY WeekDayName,WeekDayNumber ORDER BY WeekDayNumber

OUTPUT:

CountOf     WeekDayName
----------- ------------------------------
2           Monday
2           Tuesday
3           Wednesday
3           Thursday
2           Friday
2           Saturday
2           Sunday

(7 row(s) affected)

this will give a count of Monday to Friday days:

SET DATEFIRST 1
DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate='7/13/2011'
      ,@EndDate='7/28/2011'
;with AllDates AS
(
    SELECT @StartDate AS DateOf, datepart(weekday,@StartDate) AS WeekDayNumber
    UNION ALL
    SELECT DateOf+1, datepart(weekday,DateOf+1)
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT COUNT(*) AS WeekDayCount FROM AllDates WHERE WeekDayNumber<=5

OUTPUT:

WeekDayCount
------------
12

(1 row(s) affected)

If you have a holiday table, you can join it in and remove those as well. Here is a slightly different version that may preform better:

SET DATEFIRST 1
DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate='7/13/2011'
      ,@EndDate='7/28/2011'
;with AllDates AS
(
    SELECT @StartDate AS DateOf, datepart(weekday,getdate()) AS WeekDayNumber
    UNION ALL
    SELECT DateOf+1, (WeekDayNumber+1) % 7
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT COUNT(*) AS WeekDayCount FROM AllDates WHERE WeekDayNumber>0 AND WeekDayNumber<6
--I don't like using "BETWEEN", ">", ">=", "<", and "<=" are more explicit in defining end points

produces same output as original query.

There's a maximum recursion in SQL Server of 100, if I have a date that is more than 100 days apart... it fails. — Steph, Jul 13 '11 at 20:09
@Steph just add `OPTION (MAXRECURSION 0)` to the end of the query — KM., Jul 13 '11 at 20:21
I just noticed this on an above post. I was not aware until now that this was a configurable option. Thanks! — Steph, Jul 13 '11 at 20:40
@Steph `OPTION (MAXRECURSION 0)` means no limit, where `OPTION (MAXRECURSION 500)` limits it to 500. — KM., Jul 14 '11 at 13:16

score 3 · Answer 3 · answered Jul 13 '11 at 19:52

This assume standard settings but cans be adapted

DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='20110601', @EndDate='20110630'

;WITH AllDates AS
(
    SELECT @StartDate AS DateOf, datepart(weekday, @StartDate) AS WeekDayNumber
    UNION ALL
    SELECT DateOf+1, datepart(weekday, DateOf+1) 
    FROM AllDates
    WHERE DateOf < @EndDate
)
SELECT SUM(CASE WHEN WeekDayNumber BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS WeekDayCount
FROM AllDates
OPTION (MAXRECURSION 0)

Ahh... maxrecursion is an option, this i didn't know. – Steph Jul 13 '11 at 20:39 — Steph, Jul 13 '11 at 20:39

Clockwork-Muse · Answer 4 · 2011-07-13T21:10:45.407

This should be valid for SQL Server, and should be internationlization safe (note: I do not have a server to test this against).

SELECT datediff(day, @start, @end) - datediff(week, @start, @end) * 2 
                                   - CASE WHEN datepart(weekday, @start) 
                                               IN (datepart(weekday, '1970-01-03'), 
                                                   datepart(weekday, '1970-01-04')) 
                                          THEN 1
                                          ELSE 0 END, 
                                   - CASE WHEN datepart(weekday, @end) 
                                               IN (datepart(weekday, '1970-01-03'), 
                                                   datepart(weekday, '1970-01-04')) 
                                          THEN 1
                                          ELSE 0 END

Give that a whirl.

Given the clarification, this should get the number of each of the days.
Uses no recursion, and should be completely international-safe. You will have to adjust start/end date parameters for inclusion/exclusion as necessary (The DB2 version I was using to check this excluded the start date, but included the end date, for example).

WITH dayOfWeek (name, dayNumber) as (VALUES(dayname(weekday, '1970-01-01'), daypart(weekday, '1970-01-01')), 
                                           (dayname(weekday, '1970-01-02'), daypart(weekday, '1970-01-02')), 
                                           (dayname(weekday, '1970-01-03'), daypart(weekday, '1970-01-03')), 
                                           (dayname(weekday, '1970-01-04'), daypart(weekday, '1970-01-04')), 
                                           (dayname(weekday, '1970-01-05'), daypart(weekday, '1970-01-05')), 
                                           (dayname(weekday, '1970-01-06'), daypart(weekday, '1970-01-06')), 
                                           (dayname(weekday, '1970-01-07'), daypart(weekday, '1970-01-07')))
SELECT name, dayNumber, datediff(weeks, @start, @end)
                        + CASE WHEN datepart(weekday, @end) >= dayNumber THEN 1 ELSE 0 END
                        - CASE WHEN datepart(weekday, @start) >= dayNumber THEN 1 ELSE 0 END
FROM dayOfWeek

Does that help any?

I'm not at all understanding how that's going to tell me how many Mondays are between two dates, and if I'm reading correctly, it's excluding Saturdays and Sundays which I is unnecessary. — Steph, Jul 13 '11 at 20:16
I (and a number of the other posters) read your question as asking for the count of the week-days (monday through friday) between two dates. — Clockwork-Muse, Jul 13 '11 at 20:25
I understand, I should have been more clear with what I was looking for. — Steph, Jul 13 '11 at 20:40

score 0 · Answer 5 · answered Feb 13 '14 at 16:25

I would have simply added as a comment to the marked answer, but do not have enough "reputation". Instead of hardcoding the day_number which is dependent on datefirst, you could set it by discovering the weekday for each day of the week:

;WITH Days_Of_The_Week AS (
        SELECT DATEPART(dw, '2007-01-01') AS day_number, 'Monday' AS day_name UNION ALL -- 2007-01-01 is a known Monday
        SELECT DATEPART(dw, '2007-01-02') AS day_number, 'Tuesday' AS day_name UNION ALL
        SELECT DATEPART(dw, '2007-01-03') AS day_number, 'Wednesday' AS day_name UNION ALL
        SELECT DATEPART(dw, '2007-01-04') AS day_number, 'Thursday' AS day_name UNION ALL
        SELECT DATEPART(dw, '2007-01-05') AS day_number, 'Friday' AS day_name UNION ALL
        SELECT DATEPART(dw, '2007-01-06') AS day_number, 'Saturday' AS day_name UNION ALL
        SELECT DATEPART(dw, '2007-01-07') AS day_number, 'Sunday' AS day_name
    )

score 0 · Answer 6 · answered Aug 19 '17 at 07:34

@start_date date = '2017-08-11',    
@end_date date = '2017-08-27',    
@weekday int = 7,    
@count int output    

As    

Begin    

Declare @i int = 0
set @count = 0
while(@i <= (select Datediff(Day, @start_date, @end_date)))
begin
    if(Dateadd(Day, @i, @start_date) > @end_date)
        break
    if(Datepart(weekday, Dateadd(Day, @i, @start_date)) = @weekday)
        set @count += 1
    set @i += 1
end
select @count

score 0 · Answer 7 · answered Jul 13 '11 at 19:48

0

you can use DATEDIFF and DATEPART functions + some basic math to get the desired result without looping.

answered Jul 13 '11 at 19:48

gsharp

27,557
22
88
134

2

I heard that you can also build nuclear weapons by using some basic science on plutonium. – Aaron Bertrand Jul 13 '11 at 20:07
:-) Yes a very general answer that does not help me much unfortunately. – Steph Jul 13 '11 at 20:41
lol. but serious. with datediff get the count of days between 2 dates. and with datepart getting the offset when to start counting the mondays, tuesdays etc. it's easy :-) – gsharp Jul 15 '11 at 04:46

Get number of weekdays (Sundays, Mondays, Tuesdays) between two dates SQL

7 Answers7

Linked