SQL Exercise – 22

SQL Exercise:

Create a function that accepts a start date, an end date, and a day of the week (e.g., “Monday”). The function should return a table listing all dates within the specified range that match the given day of the week.

Solution:

CREATE FUNCTION GetDatesByWeekday(
@StartDate DATE,
@EndDate DATE,
@WeekdayName VARCHAR(20)
)

RETURNS @DatesTable TABLE (
MatchingDate DATE
)
AS

BEGIN

DECLARE @CurrentDate DATE = @StartDate;

WHILE @CurrentDate <= @EndDate

BEGIN
IF DATENAME(WEEKDAY, @CurrentDate) = @WeekdayName

BEGIN
INSERT INTO @DatesTable (MatchingDate)
VALUES (@CurrentDate);

END;


SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);

END;
RETURN;
END;

Ouptut:

Let’s run the function to find all Mondays between 2024-12-01 and 2024-12-14.

You will see that it returns the dates 2024-12-02 and 2024-12-09.

Explanation:

1. Parameters:
   • @StartDate: The beginning of the date range.
   • @EndDate: The end of the date range.
   • @WeekdayName: The day of the week to filter (e.g., “Monday”).
2. @DatesTable Table Variable:
   • Stores the dates that match the specified day of the week.
3. Logic:
   • A loop (WHILE) iterates through each day in the range.
   • For each date, DATENAME(WEEKDAY, @CurrentDate) retrieves the weekday name.
   • If the weekday name matches the input @WeekdayName, the date is inserted into the @DatesTable.
4. Result:
   • The function returns all dates within the specified range that match the given weekday.