SQL SERVER - How To List all Specified Week Days Between two given Dates

There will be some situations, where we need to list all Specified Weekdays between two given dates. Let us suppose, we need to list all the Sundays between two given dates. This Can be achieved by using the Common Table expression(CTE).

Suppose Our requirement is to pick all Sundays between 1st Jan 2009 and 30th Jan 2009.

For this, We can try like this :

Declare @StartDate Datetime, @EndDate Datetime

Set @StartDate = '01/01/2009'
Set @EndDate = '01/30/2009';

WITH WeekDay(FirstDay,NextDay) AS
(SELECT @StartDate AS StartDate,                       DATEADD(d,1,@StartDate) AS NextDay
UNION ALL
SELECT NextDay AS FirstDay,                                   DATEADD(D,1,NextDay) AS NextDay FROM WeekDay
WHERE NextDay BETWEEN @StartDate AND @EndDate)

SELECT FirstDay 'Sundays' FROM WeekDay WHERE   DATENAME(DW,FirstDay) = 'Sunday'

First Of all, we have declared @StartDate and @EndDate as Datetime Data type variables

Now We have used the Common Table Expression to retrieve all the days in between the @StartDate and @EndDate.

In the Next step, we retrieved all Sundays from this Common Table Expression using the DATENAME() function.

If we want to place this in a procedure, then it will look like this as below:

Create Proc List_All_Sundays(@startdate datetime,@enddate datetime) AS

Begin

WITH WeekDays(FirstDay,NextDay) AS                     (SELECT @StartDate AS StartDate,DATEADD(d,1,@StartDate) AS NextDay                                   UNION ALL                                                             SELECT NextDay AS FirstDay, DATEADD(D,1,NextDay) AS NextDay FROM WeekDays                                       WHERE NextDay BETWEEN @StartDate AND @EndDate)

SELECT FirstDay 'Sundays' FROM WeekDays WHERE DATENAME(DW,FirstDay) = 'Sunday'

End

Now Let us execute this Procedure and check the result

Exec List_All_Sundays '01/01/2009','01/31/2009'

The Output is :