SQL SERVER – Find Business Days Between Dates

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.

• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• COUNT(*) and Index – SQL in Sixty Seconds #175
• Index Scans – Good or Bad? – SQL in Sixty Seconds #174
• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)