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

Why are you using `str_to_date()` with a literal date, just enter the date in the right format `YYYY-MM-DD` — RiggsFolly, Dec 03 '20 at 17:35
None of the last STR_TO_DATEs returns NULL. What exactly is your issue? — forpas, Dec 03 '20 at 17:36
Do you see something like this for example... `Level: Warning Code: 1411 Message: Incorrect datetime value: '00/00/0000' for function str_to_date` — , Dec 03 '20 at 18:03

Gordon Linoff · Answer 1 · 2020-12-03T18:21:53.560

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.

date_created is stored as "DATE" type, but im using order 'month / day / year', that could be messing the query? — Christopher Martinez, Dec 03 '20 at 17:35

score 1 · Accepted Answer · edited Dec 16 '20 at 03:22

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 ;

mysql date conversion returns null converting certain months

2 Answers2