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