I have separate work_day and nonwork_day tables, for assorted reasons, including that I think it is simpler and very efficient (esp. when I need to see only nonwork days). Code below, first table creates/loads and then the working days calc.
I admit that we don't include time directly in our date calcs. If we need to, we adjust the dates passed into the COUNT() to account for times that exceed a limit, but even that is very rare in our case; we may just be lucky in that we don't have to worry about it.
Edit: Yes, in real life the nonwork_dates table has a few additional columns, including an encoded tinyint value for why it's a nonwork date.
/*load the date tables*/
CREATE TABLE dbo.nonwork_dates (
nonwork_date date NOT NULL,
CONSTRAINT nonwork_dates__PK PRIMARY KEY CLUSTERED ( nonwork_date ) WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
INSERT INTO dbo.nonwork_dates VALUES
CREATE TABLE dbo.work_dates (
work_date date NOT NULL,
CONSTRAINT work_dates__PK PRIMARY KEY CLUSTERED ( work_date ) WITH ( FILLFACTOR = 100 ) ON [PRIMARY]
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
cte_tally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
INSERT INTO dbo.work_dates ( work_date )
SELECT DATEADD(DAY, t.number - 1, '20200101') AS work_date
FROM cte_tally1000 t
WHERE t.number BETWEEN 1 AND 366 AND
DATEDIFF(DAY, 0, DATEADD(DAY, t.number - 1, '20200101')) % 7 <= 4 AND
NOT EXISTS(SELECT 1 FROM dbo.nonwork_dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number - 1, '20200101'))
ORDER BY 1
/*actual query to calc work days*/
/*of course it could be moved to a function, but I don't really see the need for that*/
SELECT start_date, end_date,
WHERE work_date >= start_date AND work_date <= end_date) AS work_days_count
SELECT CAST('20200101' AS date) AS start_date, CAST('20200105' AS date) AS end_date
SELECT '20200101', '20201231'
) AS test_dates
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."