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 the calculation work is done in sql server stored procedures, 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 a given solution, it seemed the best idea to go with second solution answer from msdn forum post on how to find the weekend days in sql server 2008 . the idea is simple: we create common table expressions (cte) for given year dates and select out only those where the name of week day is saturday or sunday.

but what about the calendar table? the customer has in the 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 the green-field project can also consider using calendar tables like shown in article .

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 the previous script to write a function that returns the 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 stored procedure that returns data for the report.

wrapping up

although it is usually a good idea to use calendar tables that we can fill automatically, it is not always an option. it is also possible that it is overkill for your given solution. using ctes 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 a 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.