Return SQL rows between from_date and to_date regardless of year in SQL only matching day and month not year

How to Return SQL Rows Between from_date and to_date Regardless of Year in SQL (Matching Day and Month Only, Not Year)

When working with date ranges in SQL, you may encounter scenarios where you need to retrieve rows between two dates, but you're only interested in matching the day and month, not the year. This can be useful for tasks like comparing data from the same period across different years.

Method: To achieve this, you can use the following steps:

• Extract Day and Month Components: Use the DAY() and MONTH() functions to extract the day and month components from both the from_date and to_date.
• Create Temporary Tables: Create two temporary tables, temp_from_date and temp_to_date, to store the extracted day and month components.
• Join Temporary Tables: Perform an INNER JOIN between the temporary tables on the day and month columns. This will give you rows where the day and month match between the two dates, regardless of the year.
• Join with Original Table: Join the result of the previous step with your original table using the original date columns. This will give you the rows that satisfy the day and month conditions from the original table.

Example: Consider the following example:

-- Create temporary tables to extract day and month components
CREATE TEMP TABLE temp_from_date AS
SELECT DAY(from_date) AS day, MONTH(from_date) AS month FROM your_table;

CREATE TEMP TABLE temp_to_date AS
SELECT DAY(to_date) AS day, MONTH(to_date) AS month FROM your_table;

-- Join temporary tables on day and month
SELECT *
FROM temp_from_date AS from_date
INNER JOIN temp_to_date AS to_date ON from_date.day = to_date.day AND from_date.month = to_date.month;

-- Join the result with the original table
SELECT *
FROM your_table
INNER JOIN (
    SELECT *
    FROM temp_from_date AS from_date
    INNER JOIN temp_to_date AS to_date ON from_date.day = to_date.day AND from_date.month = to_date.month
) AS date_range ON your_table.from_date = date_range.from_date AND your_table.to_date = date_range.to_date;

This query will return all rows from the your_table table where the day and month of the from_date and to_date columns match, regardless of the year.

Conclusion: By using the DAY() and MONTH() functions, creating temporary tables, and performing joins, you can easily retrieve rows between two dates based on day and month without considering the year. This technique can be applied to various scenarios where you need to compare data from specific periods across different years.