Week Days In Month

How many business days are in a given month? This scalar function takes any date and counts the weekdays (Monday through Friday) in that month. The math is: total days, minus two for each full week inside the month, minus one if the month starts on a Sunday, minus one if it ends on a Saturday.

Example: SELECT dbo.WeekDaysInMonth('2024-03-15') -- returns 21

CREATE FUNCTION WeekDaysInMonth
(
	@Date DATE
)
RETURNS int
AS
BEGIN
    DECLARE @Start DATE = DATEFROMPARTS(YEAR(@Date), MONTH(@Date), 1);
    DECLARE @End DATE = EOMONTH(@Start);

    RETURN
    (
        DATEDIFF(DAY, @Start, @End) + 1
        - (DATEDIFF(WEEK, @Start, @End) * 2)
        - CASE WHEN DATENAME(WEEKDAY, @Start) = 'Sunday' THEN 1 ELSE 0 END
        - CASE WHEN DATENAME(WEEKDAY, @End) = 'Saturday' THEN 1 ELSE 0 END
    )
END
GO