Find all weekends between two dates in SQL Server

Q: How to find all weekends (Saturdays and Sundays) between two given dates?

This is a very commonly asked interview question. Lets write down the query using 3 different methods.

Using CTE:

Know more about recursive CTE

Using WHILE loop:

Using master..spt_values:

WEEKENDSQ: Find all dates between two dates?
It can be answered by tweaking up little bit of above queries..

There are ofcourse many other solutions to this question, so if you know any, then please share it up here in the comment section, I will add those into the current post with all credit.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply