mysql date conversion returns null converting certain months

Question

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 1

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 ;

Answer 2

The problem with your query is the date format. Lowercase '%y' matches a two digit year. So, only the first two characters from 2021 are used for the year -- and you have the wrong year.

But, that is not the real problem. You don't need str_to_date(). Just use properly formatted date literals.

Assuming that the dates are stored correctly as date data types, then you can simply use:

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

If date_created is stored as a string, then fix your data model so it is either a date or datetime. Dates should not be stored as strings.