mysql date conversion returns null converting certain months

I have this query (take a look on between dates):

SELECT user_name, COUNT(*) AS 'COUNT' 
FROM user_records 
WHERE date_created between (STR_TO_DATE('11/24/2020','%m/%d/%y'))  
                and (STR_TO_DATE('12/26/2021','%m/%d/%y')) 
GROUP BY user_name ;

The select is between dates:

startDate: (STR_TO_DATE(’11/24/2020′,’%m/%d/%y’))

finishDate: (STR_TO_DATE(’12/26/2021′,’%m/%d/%y’))

This query will return something because there are records on year 2020

the problem is when i change the month of the finishDate, i tried with:

finishDate: (STR_TO_DATE(‘1/26/2021′,’%m/%d/%y’)) = null

finishDate: (STR_TO_DATE(’01/26/2021′,’%m/%d/%y’)) = null

finishDate: (STR_TO_DATE(’10/26/2021′,’%m/%d/%y’)) = null

It just makes no sense… im using mysql community 8.0.20

Answer

Since the problem only occurs in the finsihDate perhaps this could be helpful.

SELECT user_name, COUNT(*) AS 'COUNT' 
FROM user_records 
WHERE date_created between (STR_TO_DATE('11/24/2020','%m/%d/%y'))  
                and (DATE_ADD(STR_TO_DATE('11/24/2020','%m/%d/%y'), INTERVAL 367 DAY)) 
GROUP BY user_name ;

Of course you should check for relevant errors or warnings in MySQL server logs, that could explain the problem for finsihDate.

********UPDATE SOLUTION: for some unknown reason my db IDE shows the date with this format “$DAY/$MONTH/$YEAR” even if insert the right DATE MYSQL FORMAT (“$YEAR-$MONTH-$DAY)

i got the following warnings:

And this is the final query that worked but your solution did worked as well:

SELECT user_name, COUNT(*) AS 'COUNT' 
FROM user_records 
WHERE date_created between '2020-11-24' AND '2021-01-24' 
GROUP BY user_name ;