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

Question: How to Count Week Days Between Two Dates?

How to Count Week Days Between Two Dates? - Interview Question of the Week #132 weekdays-800x212

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)

Quest

SQL DateTime, SQL Scripts, SQL Server
Previous Post
What is the Default Datatype of NULL? – Interview Question of the Week #131
Next Post
How is Oracle Temporary Table Different from SQL Server? – Interview Question of the Week #133

Related Posts

6 Comments. Leave new

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

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

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

    first one doesn’t work from monday to friday.

    Reply
  • Chuck Newman, Jr
    April 4, 2018 11:18 pm

    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.

    Reply

Leave a Reply