Fetch dates within the next 5 days from today, from a column of dates

Question

I have a table with dates from last year:

ID  start_date
1   2020-09-06 11:21:00
2   2020-10-12 01:43:00
3   2020-09-07 17:22:00
4   2020-11-23 04:23:00
5   2020-09-09 19:50:00

If today is 2020-09-05

I need to fetch rows which start_date +1 year are in the next 5 days from today.

From the sample table, the desired result would be:

1   2020-09-06 11:21:00 (Because 2020-09-06 +1 year = 2021-09-06 is in within 5 days from today)
3   2020-09-07 17:22:00 (Because 2020-09-07 +1 year = 2021-09-07 is in within 5 days from today)
5   2020-09-09 19:50:00 (Because 2020-09-09 +1 year = 2021-09-09 is in within 5 days from today)

I have this query:

SELECT ID, start_date 
FROM my_table 
WHERE 
DATE( "start_date +1 year" ) >= NOW()
AND
DATE( "start_date +1 year" ) <= NOW("+5 days")

But it is not working. What can I try next?

Answer 1

You can use INTERVAl to get the dates you need

SELECT ID, `start_date`
FROM my_table 
WHERE 
DATE(`start_date` + INTERVAL 1 YEAR)  >= CURDATE()
AND
DATE(`start_date` + INTERVAL 1 YEAR)  <= CURDATE() + INTERVAL 5 DAY

ID | start_date         
-: | :------------------
 1 | 2020-09-06 11:21:00
 3 | 2020-09-07 17:22:00
 5 | 2020-09-09 19:50:00

db<>fiddle here