SQL Query to find All Sundays Between Two Dates

To find all the Sundays in between two days using SQL Language, we will be using the “Date Functions” defined in SQL. Apart from these we will be using CTE ( View) idea too.

Basic idea: 

So basically we are given two days, and we are required to list all Sundays between these two days. 

Thinking a little we got the idea that we can check all the dates between the two boundary dates given for Sundays. Also SQL Date functions do provide one such method to implement this idea:

DATENAME(interval_part, Date)

This function return the name of the part of the date specified in arguments.

For eg : 

DATENAME(month, '18/11/2001' ) 

This call to the function would return November.

Hence we can match the name of day represented by any date with sunday. If it is sunday than we select it otherwise rejects it.

Now the left over part is to create a table which contains a column of all the dates between the two given dates, so that we can perform our check over them.

This can be done using the idea that we can increment the day part of date by 1 over the preceding date to get next date and do check that the next date is less than the given upper bound of the dates.

DATEADD(part , number, date) : This method is used to add the specified number to the given part  of the date .

For eg. DATEADD(year,2,’18/11/2001′) This results in the date : 18/11/2003.

So here is our SQL Query for this topic:

declare  @StartDate DateTime = '2021-02-01',
              @EndDate DateTime = '2021-02-28';
/*Creating a temporary view in sql(CTE) which recursively 
calls itself to find next date by incrementing the 
previous date and stores the result in it till the end date is reached*/
WITH CTE(date_list) AS (
      SELECT @StartDate
      UNION ALL
      SELECT DATEADD(day,1,date_list) FROM CTE
      WHERE date_list<=@EndDate
)
SELECT date_list as 'List of sundays'
FROM CTE
WHERE DATENAME(weekday ,date_list) IN ('Sunday');
/*In the where clause at last we are checking 
each day from the list whether it is in Sunday list or not*/