这将给予23：

SELECT
    (DATEDIFF(dd, @StartDate, @EndDate+1)) -(DATEDIFF(wk, @StartDate, @EndDate))
    -ISNULL((CASE WHEN IsAlternateOffday2 = 1 THEN (DATEDIFF(wk, @StartDate, @EndDate)) END), 0) 
FROM HRM.tbl_Shift

这样每个周末就减去了2天。检查一下你的服务器配置，看看星期天是一周的第一天还是最后一天。这可能会把事情推迟到一个周末。

SELECT DATEPART(WEEKDAY,'20180506') --Checks if Sunday is Day 1 or Day 7

DECLARE @start DATETIME = '20180501'
DECLARE @end DATETIME = '20180531'

SELECT DATEDIFF(DAY,@start,@end+1) - (DATEDIFF(WEEK,@start,@end+1)*2)

更新：
使用COALESCE将NULLS替换为替代值。

DECLARE @offdate DATETIME = NULL

SELECT COALESCE(@offdate,GETDATE())

试试这个

DECLARE @StartDate DATE ='2018-05-01',
        @EndDate DATE ='2018-05-31'
;WITH CTE
AS
(
SELECT DATEADD(DD,Number-1,@StartDate) MOnthDates,
       DATENAME(DW,DATEADD(DD,Number-1,@StartDate)) As DayNAmes,
       CASE WHEN DATENAME(DW,DATEADD(DD,Number-1,@StartDate)) IN ('Saturday','Sunday') THEN 0 ELSE 1 END WeekDays
FROM master.dbo.spt_values
WHERE [Type]='P'
AND Number Between 1 AND 10000
)
SELECT COUNT(WeekDays)  AS WeekDaysCount
FROM CTE
WHERE WeekDays<>0
AND MOnthDates Between @StartDate AND DATEADD(DAY,1,@EndDate)

结果

WeekDaysCount
-------------
23

演示：http://rextester.com/TOLYT35075

这是我在查看答案后创建的。我需要几个月的每月天数。下面将提供该信息，并将其插入到#MonthDayCount表中。注意：我将字段命名为Month，尽管这是SQL Server定义的术语;如果需要，可以更改它。
您必须输入该月的第一天和最后一天，以便正确计算第一个月和最后一个月。如果您输入1/2/2022，则会导致一月少一天。

DECLARE @StartDate DATE = '1/01/2021'--The start of the first month the number of days are needed for.
DECLARE @EndDate DATE = '1/31/2021'--The end of the first month the number of days are needed for.
DECLARE @FinalDate DATE = '12/31/2022'--This is the last month that will be inserted. Includes this month.

IF OBJECT_ID('tempdb..#MonthDayCount', 'U') IS NOT NULL
DROP TABLE #MonthDayCount; 
CREATE TABLE #MonthDayCount
                            (
                                [Month]             VARCHAR(MAX),
                                [# of WeekDays]     INT,
                                [EOM]               DATE
                            )

WHILE           @StartDate <= @FinalDate
BEGIN 
INSERT INTO     
    #MonthDayCount
                    VALUES  (
                                DATENAME(m,@StartDate),
                                (
                                    DATEDIFF(dd, @StartDate, @EndDate) + 1)
                                    -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
                                    -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
                                    -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END
                                ),
                                @EndDate
                            )

  SET @StartDate = DATEADD(m,1,@StartDate)
  SET @EndDate = EOMONTH(@EndDate,1)
 

END

MS SQL Server查询以查找当前月份中所有工作日（非星期六和星期日）的计数。在SQL Server、Azure Synapse Analytics中工作

注意：根据需要更改getdate（）。

SELECT Day(Eomonth(Getdate())) - ( Datediff(d, Dateadd(d, Datediff(d, -1,
                                               Dateadd(month, Month(
                                               Getdate()) - 1 + (
                                   Year(Getdate()
                                   ) - 1900 )
                                   *
                                   12, 6)) / 7 * 7, -1),
                                   Dateadd(m, 1, Dateadd(
                                   month,
                                                 Month(Getdate())
                                                 - 1 +
                                                 (
                                   Year(Getdate()) - 1900 )
                                                 *
                                                 12, 6
                                                 )
                                   )) / 7 +
                                          Datediff(d, Dateadd(d, Datediff(d, -1,
                                                                 Dateadd(
                                                                 month,
                                                      Month(Getdate()) - 1 + (
                                          Year(Getdate()
                                          ) - 1900 )
                                          *
                                          12, 7)) / 7 * 7, -1), Dateadd(m, 1,
                                          Dateadd(month, Month(Getdate()) - 1 +
                                                         (
                                          Year(Getdate()) - 1900 ) *
                                                              12, 7)
                                          )) / 7 ) AS Num_Working_Days