SQL SERVER – Find Business Days Between Dates

I often receive a very common question during my Comprehensive Database Performance Health Check about how to find Find Business Days Between Dates. Let us see a very simple script for it.

SQL SERVER - Find Business Days Between Dates BusinessDays1-800x181

DECLARE @StartDate AS DATE = '2021-07-01', @EndDate AS DATE = '2021-07-31'
;WITH dateCTE AS
(
     SELECT @StartDate StartDate, DATEPART(WEEKDAY,@StartDate) wkday
     UNION ALL
     SELECT DATEADD(day,1,StartDate) ,  DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday  
	 FROM dateCTE
     WHERE DATEADD(day,1,StartDate) <= @EndDate
)
SELECT COUNT(*) WeekDays 
FROM dateCTE 
WHERE wkday NOT IN(1,7) -- Execluding Sat, Sun

When you run the script above it will give results as following:

SQL SERVER - Find Business Days Between Dates BusinessDays

You can specify any other date as a start date and end date and it will give you accurate results. Let me know what you think of this blog post about Find Business Days Between Dates.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Quest

SQL DateTime, SQL Scripts, SQL Server
Previous Post
One Scan for 3 Count Sum – SQL in Sixty Seconds #178
Next Post
Feedback Request – COUNT and SUM Videos

Related Posts

2 Comments. Leave new

  • The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

    CREATE TABLE Calendar
    (cal_date DATE NOT NULL PRIMARY KEY,
    julian_business_nbr INTEGER NOT NULL,
    …);

    INSERT INTO Calendar
    VALUES (‘2007-04-05’, 42),
    (‘2007-04-06’, 43), — good Friday
    (‘2007-04-07’, 43),
    (‘2007-04-08’, 43), — Easter Sunday
    (‘2007-04-09’, 44),
    (‘2007-04-10’, 45); –Tuesday

    To compute the business days from Thursday of this week to next Tuesdays:

    SELECT (C2.julian_business_nbr – C1.julian_business_nbr)
    FROM Calendar AS C1, Calendar AS C2
    WHERE C1.cal_date = ‘2007-04-05’,
    AND C2.cal_date = ‘2007-04-10’;

    Reply
  • I’m attempting to use your script, and it’s working fine in my query, but when i attempt to build it into SSRS, it’s saying: ‘Types don’t match between the anchor and the recursive part in column “StartDate” of recursive query “dateCTE”.’

    My edits to your code:

    –Calculate Business days
    DECLARE @WeekDays INT

    ;WITH dateCTE AS
    (
    SELECT @Date1 StartDate, DATEPART(WEEKDAY,@Date1) wkday
    UNION ALL
    SELECT DATEADD(day,1,StartDate) , DATEPART(WEEKDAY, DATEADD(day,1,StartDate)) wkday
    FROM dateCTE
    WHERE DATEADD(day,1,StartDate) <= @Date2
    )
    SELECT @WeekDays = COUNT(*)
    FROM dateCTE
    WHERE wkday NOT IN(1,7) — Execluding Sat, Sun

    Reply

Leave a Reply