# Interview Question of the Week #047 – Logic To Find Weekdays Between Two Dates

I often enjoy seeing interview questions where the contestant has to write SQL Scripts. Here is another such example.

Question: How to find weekdays between two dates?

Answer: Here is a function which can be used to find weekdays between two dates.

```CREATE FUNCTION dbo.getDayCount(@startdate date, @enddate date) RETURNS INT AS BEGIN DECLARE @count INT = 0 WHILE @startdate<=@enddate BEGIN IF DATEPART(dw,@startdate) > 1 AND DATEPART(dw,@startdate) < 7 BEGIN SET @count = @count + 1 END SET @startdate = DATEADD(DAY,1,@startdate) END RETURN @count END -- Execute Function SELECT dbo.getDayCount ('10/10/2015', '11/10/2015') ```

Reference: Pinal Dave (https://blog.sqlauthority.com)

#### Related Posts

• John Paul Cook
November 29, 2015 8:31 am

That was too much work.

SELECT DATEDIFF ( d, ’10/10/2015′ , ’11/10/2015′ );

• This is shorter and cleaner answer.

November 29, 2015 4:32 pm

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

• Bachchan Singh
November 30, 2015 1:47 pm

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
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