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.
Reference: Pinal Dave (https://blog.sqlauthority.com)