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.

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:

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)

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

1 Comment. Leave new

  • Joe F Celko
    July 6, 2021 1:34 am

    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

Leave a Reply

Menu
Exit mobile version