SQL SERVER – Find Business Days Between Dates

2 years ago

I often receive a very common question during my Comprehensive Database Performance Health Check about how to find Find Business Days Between Dates. Let us see a very simple script for it.

DECLARE @StartDate AS DATE = '2021-07-01', @EndDate AS DATE = '2021-07-31'
;WITH dateCTE AS
(
     SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday
     UNION ALL
     SELECT DATEADD(day,1,StartDate) ,  DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday  
	 FROM dateCTE
     WHERE DATEADD(day,1,StartDate) <= @EndDate
)
SELECT COUNT(*) WeekDays 
FROM dateCTE 
WHERE wkday NOT IN(1,7) -- Execluding Sat, Sun

When you run the script above it will give results as following:

You can specify any other date as a start date and end date and it will give you accurate results. Let me know what you think of this blog post about Find Business Days Between Dates.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL DateTime, SQL Scripts, SQL Server

One Scan for 3 Count Sum – SQL in Sixty Seconds #178

Feedback Request – COUNT and SUM Videos

2 Comments. Leave new

Joe F Celko
July 6, 2021 1:34 am
The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
julian_business_nbr INTEGER NOT NULL,
…);
INSERT INTO Calendar
VALUES (‘2007-04-05’, 42),
(‘2007-04-06’, 43), — good Friday
(‘2007-04-07’, 43),
(‘2007-04-08’, 43), — Easter Sunday
(‘2007-04-09’, 44),
(‘2007-04-10’, 45); –Tuesday
To compute the business days from Thursday of this week to next Tuesdays:
SELECT (C2.julian_business_nbr – C1.julian_business_nbr)
FROM Calendar AS C1, Calendar AS C2
WHERE C1.cal_date = ‘2007-04-05’,
AND C2.cal_date = ‘2007-04-10’;
Reply
mwelch16
April 25, 2022 10:10 pm
I’m attempting to use your script, and it’s working fine in my query, but when i attempt to build it into SSRS, it’s saying: ‘Types don’t match between the anchor and the recursive part in column “StartDate” of recursive query “dateCTE”.’
My edits to your code:
–Calculate Business days
DECLARE @WeekDays INT
;WITH dateCTE AS
(
SELECT @Date1 StartDate, DATEPART(WEEKDAY,@Date1) wkday
UNION ALL
SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday
FROM dateCTE
WHERE DATEADD(day,1,StartDate) <= @Date2
)
SELECT @WeekDays = COUNT(*)
FROM dateCTE
WHERE wkday NOT IN(1,7) — Execluding Sat, Sun
Reply