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)
That first one only works if both dates are in the same year – the other script works regardless. When using the first one you also want to be careful of DATEFIRST – https://blog.sqlauthority.com/2007/04/22/sql-server-datefirst-and-set-datefirst-relations-and-usage/
Very good point.
First solutions doesn’t work properly and the 2nd solution doesn’t need the case statements
-(CASE WHEN DATENAME(dw, @FirstDate) = ‘Sunday’ THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @SecondDate) = ‘Saturday’ THEN 1 ELSE 0 END)
It will work fine without these two lines of code as well
For Jan-2017 Month Total Weekdays are 22 above query is showing 23..
It fails for Jan-2017 Month
first one doesn’t work from monday to friday.
A little opaque but this’ll do it:
select diff/7*5 + diff%7 + SIGN(7 – dw – diff%7) – iif(dw=1,1,0) from (select DATEDIFF(day, @FirstDate, @SecondDate) diff, DATEPART(weekday, @FirstDate) dw) t
It’s like weeks + remainder + a one day adjustment based on the remainder and the weekday of FirstDate.