SQL Get all Saturdays between two Dates

I’m trying to find all the saturdays between two dates (inclusive) without using a loop.

For example, April 1, 2021, to May 1, 2021 should return:

04-03-2021
04-10-2021
04-17-2021
04-24-2021
05-01-2021

Answer

You can use datename

    select *
    from Table
    where Datename(dw,Datecolumn)='Saturday'
    and DateColumn >= start and Datecolumn < End;

Alternatively, if you just want to generate a list on the fly, you can do

declare @StartDate date = '20210401', @EndDate date = '20210501';

select DateAdd(day, rn-1, @StartDate)
from ( 
    select Row_Number() over (order by object_id) rn
    from sys.columns
 ) d
where rn - 1 <= DateDiff(day, @StartDate, @EndDate)
and DateName(dw, DateAdd(day, rn-1, @StartDate))='Saturday';

This first generates a list of numbers in the inner query by utilising one of several possible system tables to select just a row number. This is essentially building a numbers / tally table on the fly, and it’s usually a good idea to have a permanent table like this available (a single column of integers starting at 1 and going up to a squintillion or whatever you need). You can see how this works by just highlighting the inner query and running it (F5 in SSMS).

An out query selects from this and filters the row numbers to just the right sequential range for the number of days between the two specified dates. Again, you can check this by highlighting the entire query except for the last line, you’ll see it generates the list of dates between the specified start and end dates inclusively.

Finally the and criteria extracts the name of the day for each date and further filters the list of dates based on the day’s name.