MSSQL: Finding weekend days in month

One of my applications displays tables with some calculations about users working time. This is for corporate reporting. For this table I needed a way to find how many weekends days are in given month. As most of calculation work is done in SQL Server stored procedure I decided to keep stuff there and found a way how to get weekend days with common table expression.

I looked for different options and in given solution it seemed the best idea to go with second solution answer from MSDN forum post How to find the weekend days in sql server 2008. The idea is simple: we create common table expression (CTE) for given year dates and select out only those where name of week day is Saturday or Sunday.

But what about calendar table? Customer has in database some sort of lightweight calendar table but this is only for holidays and I can use it to find out shorter working days before national holidays. Those who start with green-field project can also consider using calendar tables like shown in aricle Date and Time – Create Master Calendar Table.

Getting weekend days in month

First I modified the answer to get back weekend days in given month.

DECLARE @Date date
 
SET @Date = CONVERT(DATE, '2017-03-01')
 
;WITH CTE AS
(
    SELECT 
        @Date AS [Date], 
        MONTH(@Date) As [Month],
        DATENAME (MONTH,@Date) AS [MonthName],
        DATENAME (DW,@Date) AS [DayName]
    UNION ALL
    SELECT 
        DATEADD(DAY,1,[DATE]) AS [Date],
        MONTH(DATEADD(DAY,1,[DATE])) AS [Month],
        DATENAME (MONTH,DATEADD(DAY,1,[DATE])) AS [MonthName],
        DATENAME (DW ,DATEADD(DAY,1,[DATE])) AS [DayName] 
    FROM 
        CTE 
    WHERE 
        YEAR(DATEADD(DAY,1,[DATE]) )=YEAR(@Date)
        AND MONTH(DATEADD(DAY,1,[DATE]))=MONTH(@Date)
)
SELECT 
    *
FROM 
    CTE 
WHERE 
    [DayName] IN ('Saturday','Sunday') 
ORDER BY 
    [DATE]
OPTION 
    (MAXRECURSION 367)

Running this SQL script in SSMS gives the following output.

If needed it’s possible to add more fields or remove existing ones based on current needs.
I left all fields from my first code to visualize better what is returned by CTE.

Getting count of weekend days in month

I used previous script to write function that return count of weekend days in given month.

CREATE FUNCTION GetWeekendDaysCount
(
    @Date datetime
)
RETURNS int
AS
BEGIN
    DECLARE @WeekendDays int
 
    ;WITH CTE AS
    (
        SELECT 
            @Date AS [Date], 
            MONTH(@Date) As [Month],
            DATENAME (MONTH,@Date) AS [MonthName],
            DATENAME (DW,@Date) AS [DayName]
        UNION ALL
        SELECT 
            DATEADD(DAY,1,[DATE]) AS [Date],
            MONTH(DATEADD(DAY,1,[DATE])) AS [Month],
            DATENAME (MONTH,DATEADD(DAY,1,[DATE])) AS [MonthName],
            DATENAME (DW ,DATEADD(DAY,1,[DATE])) AS [DayName] 
        FROM 
            CTE 
        WHERE 
            YEAR(DATEADD(DAY,1,[DATE]) )=YEAR(@Date)
            AND MONTH(DATEADD(DAY,1,[DATE]))=MONTH(@Date)
    )
    SELECT 
        @WeekendDays = COUNT(*)
    FROM 
        CTE 
    WHERE 
        [DayName] IN ('Saturday','Sunday') 
    OPTION 
        (MAXRECURSION 367)
 
    RETURN @WeekendDays
END

Notice that for count there is no need for ORDER BY. To try out the function we can use the following SQL script.

DECLARE @Date date
 
SET @Date = CONVERT(DATE, '2017-03-01')
 
SELECT dbo.GetWeekendDaysCount(@Date)

It returns correctly 8 as a result. I can use the same call also in my tored procedure that returns data for report.

Wrapping up

Although it is usually good idea to use calendar tables that we can fill automatically it is not always an option. It is also possible it is overkill for given solution. Using CTE-s to find weekend days in given month is actually easy although the code doesn’t look simple for SQL beginners or application developers like me. Still I consider this solution good one as finding weekend days happens in database and there’s no need for WHILE loops and cursors. The code given here is easy to modify to meet other needs related to days of given month.