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