# 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)

## SQL SERVER – DMV – sys.dm_os_wait_stats Explanation – Wait Type – Day 3 of 28

• 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/

• 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

• Chuck Newman, Jr
April 4, 2018 10:08 pm

first one doesn’t work from monday to friday.