mysqlsqlstr-to-date

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

enter image description here

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

Solution

  • 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:

    enter image description here

    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 ;