unable to extract date in Mysql when using in where clause in the imdb dataset-CodePudding

This code is ending up with 0 values:

SELECT count(m.id), median_rating
FROM movie as m 
INNER JOIN ratings as r ON m.id=r.movie_id
where  median_rating=8 
AND date_published between str_to_date('01-04-2018','%d-%m-%y') 
                       AND str_to_date('2019-04-01','%y-%m-%d');

Also for Column: date_published Definition is: date_published date

So I want to extract the date in date format from the texts so that I can use it while filtering but its not working, I tried with string format of dates and the code is working ( WHERE median_rating = 8 AND date_published BETWEEN '2018-04-01' AND '2019-04-01' ) but its not working when I try to extract the dates and compare :/

CodePudding user response：

See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

%Y  Year, numeric, four digits
%y  Year, numeric (two digits)

Using the code for two-digit years does not match your input values.

Demo:

select str_to_date('01-04-2018','%d-%m-%y') as date;
 ------------ 
| date       |
 ------------ 
| 2020-04-01 |
 ------------ 
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect date value: '01-04-2018'

select str_to_date('2019-04-01','%y-%m-%d');
 -------------------------------------- 
| str_to_date('2019-04-01','%y-%m-%d') |
 -------------------------------------- 
| NULL                                 |
 -------------------------------------- 
1 row in set, 1 warning (0.00 sec)

Warning (Code 1411): Incorrect datetime value: '2019-04-01' for function str_to_date

Both dates can be parsed if you use the format code for four-digit year, %Y.