Mysql: Using str_to_date on a select query returns nothing

Question

I've following Mysql query:

select str_to_date((select distinct cast(substr(tb2.sub1,1,4) AS CHAR) as year from (
SELECT SUBSTRING_INDEX(file_name,'_',-1) as sub1 from table2) as tb2) , '%Y')

And it is correct because mysqlworkbench returns green flag but no output.

Could you help me?

Answer 1

The expression

        select str_to_date('2007', '%Y')

returns 2007-00-00. Some MySQL servers are set to disallow invalid dates. Try using

        select makedate('2007', 1) 

instead. That will give you the valid date of 2007-01-01.

I'm leaving it to you to edit your query to make the change.