Question: How can I count weekdays between two dates in SQL Server? Count Monday through Friday in an explicitly inclusive date range. Don’t let the connection’s DATEFIRST setting quietly redefine the weekend.
I enjoy interview questions that ask someone to write a small piece of SQL rather than recite a definition. Walking through the dates one by one makes the logic easy to discuss, so here is that walk with a weekday test that doesn’t depend on session settings.
DECLARE @Start date=CONVERT(date,'20151010',112),
@End date=CONVERT(date,'20151110',112);
DECLARE @Day date=@Start,@Weekdays int=0;
IF @Start IS NULL OR @End IS NULL
SET @Weekdays=NULL;
ELSE
WHILE @Day<=@End
BEGIN
-- 1900-01-01 was Monday. Normalize negative remainders too.
IF ((DATEDIFF(day,CONVERT(date,'19000101',112),@Day)%7)+7)%7<5
SET @Weekdays+=1;
IF @Day=@End BREAK; -- Do not add a day past 9999-12-31.
SET @Day=DATEADD(day,1,@Day);
END;
SELECT @Start AS StartDate,@End AS EndDate,@Weekdays AS Weekdays;The result is 22 weekdays, including both 10 October and 10 November 2015. The first date is a Saturday and doesn’t contribute. The last is a Tuesday and does.
Why Not DATEPART(dw)?
The test you see most often is DATEPART(dw,@Day)>1 AND DATEPART(dw,@Day)<7. It treats 1 and 7 as weekend days only when Sunday is first. With DATEFIRST 1, it wrongly excludes Monday and includes Saturday.
The test above measures days from a known Monday. The normalized remainder is 0 through 4 for weekdays and 5 or 6 for the weekend, even for dates before 1900. It also uses explicit dates instead of an ambiguous string such as '10/10/2015'.
| Tested input | Weekdays |
|---|---|
| 10 Oct to 10 Nov 2015, DATEFIRST 1 | 22 |
| Same range, DATEFIRST 7 | 22 |
| 20 Nov 2015, Friday only | 1 |
| 21 Nov 2015, Saturday only | 0 |
| 10 Nov back to 10 Oct 2015 | 0 |
| NULL starting date | NULL |
| 31 Dec 9999, one day | 1 |
I ran each of these inputs on SQL Server 2025, under both DATEFIRST settings.
Define the Boundaries Before Turning It Into a Function
This example returns NULL for a missing boundary and zero for a reversed range. A same-day Friday counts as one; a same-day Saturday counts as zero. It breaks on the final date before incrementing, so an end date of 31 December 9999 doesn’t overflow.
You can place the same logic inside a reusable scalar function. For repeated calculations over many rows, use a calendar table and a set-based count rather than walking the range for every row. A calendar table also lets you exclude company holidays; Monday through Friday alone is not a complete business-day rule.
DATEPART(dw) is not a portable weekday test, it is a test that depends on DATEFIRST, so measure from a known Monday.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
13 Comments. Leave new
That was too much work.
SELECT DATEDIFF ( d, ’10/10/2015′ , ’11/10/2015′ );
This is shorter and cleaner answer.
The DATEDIFF solution in the comments is the number of days between 2 dates which doesn’t answer the original question which is the number of weekdays (Mon – Fri) between the dates?
Also, in the getDayCount function solution, wouldn’t you have to set datefirst at the beginning to ensure that your weekend does fall on days 1 and 7?
How come above is correct answer? Asked workdays only. Datediff() function gives no. of days only.
Your command counts every day between the dates. The posted function counts only working days, monday – friday without saturday and sunday.
Heiko is correct. Pinals function counts ONLY weekdays. The DATEDIFF counts ALL days.
HOWEVER In Pinals function – DATEPART(dw… depends on the following setting being 7 as his greater than assumes DATEFIRST is at its default value of 7. If you’re getting unexpected results; try checking @@DATEFIRST
SELECT @@DATEFIRST
SET DATEFIRST 7
This will display the days between the start date and end date, not provide the weekdays.
— alternate way
DECLARE @startDate DATETIME, @endDate DATETIME
SELECT @startDate = ‘2015/10/10’, @endDate = ‘2015/11/10’ ;
;WITH AllDays
AS(
SELECT @startDate AS WorkDay, DATEDIFF(DAY, @startDate, @endDate) AS DiffDays
UNION ALL
SELECT DATEADD(dd, 1, WorkDay),DiffDays -1
FROM AllDays wd
WHERE DiffDays > 0
)
SELECT COUNT(*)
FROM AllDays
WHERE DATEPART(dw, WorkDay ) IN (2, 3, 4, 5, 6);
Hello,
I got 22 instead of 1.
check and verify. thanks
Heiko is correct. Pinals function counts ONLY weekdays. The DATEDIFF counts ALL days.
HOWEVER In Pinals function – DATEPART(dw… depends on the following setting being 7 as his greater than assumes DATEFIRST is at its default value of 7. If you’re getting unexpected results; try checking @@DATEFIRST
SELECT @@DATEFIRST
SET DATEFIRST 7
What’s with all the looping or even-more-expensive recursion?? Use a simple math calc instead. Also, for efficiency, get rid of all local variables and just use a single RETURN statement.
CREATE FUNCTION dbo.GetWeekDaysCount (
@fromDate datetime,
@toDate datetime
)
–SELECT dbo.GetWeekDaysCount(‘20151010’, ‘20151110’)
RETURNS int
BEGIN
RETURN (
–declare @fromdate datetime, @todate datetime select @fromdate = ‘20151010’, @todate = ‘20151110’
SELECT *,
(days / 7 * 5) + (days % 7) –workdays in whole weeks (if any) plus total days in partial week (if any)
– CASE WHEN 6 BETWEEN wkdy AND wkdy + days % 7 – 1 THEN 1 ELSE 0 END –minus 1 if partial week includes Saturday
– CASE WHEN 7 BETWEEN wkdy AND wkdy + days % 7 – 1 THEN 1 ELSE 0 END –minus 1 if partial week includes Sunday
FROM (
SELECT
DATEDIFF(DAY, @fromDate, @toDate) + 1 AS days, –total days between the two dates
DATEDIFF(DAY, 0, @fromDate) % 7 + 1 AS wkdy –dayofweek: 1=Mon,…,6=Sat,7=Sun REGARDLESS OF SQL SETTINGS!
) AS derived
)
END –FUNCTION
CREATE FUNCTION dbo.GetWeekDaysCount (
@fromDate datetime,
@toDate datetime
)
–SELECT dbo.GetWeekDaysCount(‘20151010’, ‘20151110’)
RETURNS int
BEGIN
RETURN (
–declare @fromdate datetime, @todate datetime select @fromdate = ‘20151010’, @todate = ‘20151110’
SELECT *,
(days / 7 * 5) + (days % 7) –workdays in whole weeks (if any) plus total days in partial week (if any)
– CASE WHEN 6 BETWEEN wkdy AND wkdy + days % 7 – 1 THEN 1 ELSE 0 END –minus 1 if partial week includes Saturday
– CASE WHEN 7 BETWEEN wkdy AND wkdy + days % 7 – 1 THEN 1 ELSE 0 END –minus 1 if partial week includes Sunday
FROM (
SELECT
DATEDIFF(DAY, @fromDate, @toDate) + 1 AS days, –total days between the two dates
DATEDIFF(DAY, 0, @fromDate) % 7 + 1 AS wkdy –dayofweek: 1=Mon,…,6=Sat,7=Sun REGARDLESS OF SQL SETTINGS!
) AS derived
)
END –FUNCTION
declare @date1 datetime2= ’10/10/2015′, @date2 datetime2=’11/10/2015′;
with cte1(dates)
as
(
select @date1 as dates
union all
select dateadd(dd,1,dates) as dates
from cte1
where dates< @date2
)select sum(case when datepart(weekday,dates) in (6,7) then 0 else 1 end ) as cnt from cte1;