How to Count Week Days Between Two Dates? – Interview Question of the Week #132

Question: How to Count Week Days Between Two Dates?

Answer: Some questions are so simple and interesting that it is fun to build a SQL Script for them.

When I searched for an answer to this question, I found quite a few answers online. Actually, all were great answer, but I decided that I want to build an answer from scratch and I come up with following solution.

Let us see answer to above question here.

DECLARE @FirstDate DATETIME
SET @FirstDate = '2017/07/01'
DECLARE @SecondDate DATETIME
SET @SecondDate = '2017/07/31'

SELECT COUNT(DISTINCT number)
FROM master..spt_values
WHERE 
 CAST(number AS INT) 
	BETWEEN DATEPART(dy, @FirstDate) AND DATEPART(dy, @SecondDate)
AND (DATEPART(dw, DATEADD(d, number, @FirstDate)) IN (2,3,4,5,6))
GO

Well, that’s it. You can just change the parameters in the To and From and get the necessary answer.

Here is another script which I have found in so many different places on the internet, I am not sure who originally wrote the script.

DECLARE @FirstDate DATETIME
SET @FirstDate = '2017/07/01'
DECLARE @SecondDate DATETIME
SET @SecondDate = '2017/07/31'
SELECT
(DATEDIFF(dd, @FirstDate, @SecondDate) + 1)
-(DATEDIFF(wk, @FirstDate, @SecondDate) * 2)
-(CASE WHEN DATENAME(dw, @FirstDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @SecondDate) = 'Saturday' THEN 1 ELSE 0 END)
As NoOfWeekDays

Above script also gives the same answer.

Both of the scripts give us the same answer. You should use the script which you find easy to use.

Here is another very related article on this subject. Please let me know your thoughts in the comments.

SQL SERVER – Get Date of All Weekdays or Weekends of the Year

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