Date comparison confusion in MySQL - Read the example below

MySQL is pretty straight forward in the documentation. All valid dates should be in 'YYYY-MM-DD' format or 'yy-mm-dd' format.

But developers like us will continue making mistakes and hence learn the hard way.

As we see in the below example, how the format matters in date comparisons.

Example code:

SELECT 

fn_str_date_chk(efer.eligibility_end_date)

,date_format(str_to_date(efer.eligibility_end_date,'%m%d%Y'),'%Y-%m-%d') as end_Date

,date_format(str_to_date('11012014','%m%d%Y'),'%Y-%m-%d') as file_date

, case when date_format(str_to_date(efer.eligibility_end_date,'%m%d%Y'),'%Y-%m-%d') < date_format(str_to_date('11012014','%m%d%Y'),'%Y-%m-%d') then 1 else 0 end as newresult

,date_format(str_to_date(efer.eligibility_end_date,'%m%d%Y'),'%m%d%Y') as end_Date

,date_format(str_to_date('11012014','%m%d%Y'),'%m%d%Y') as file_date

, case when date_format(str_to_date(efer.eligibility_end_date,'%m%d%Y'),'%m%d%Y') < date_format(str_to_date('11012014','%m%d%Y'),'%m%d%Y') then 1 else 0 end as oldresult

,efer.*

FROM eligibility_file_error_row as efer

WHERE (fn_str_date_chk(eligibility_end_date) = 1 AND fn_str_date_chk(eligibility_effective_date) = 1)

AND (date_format(str_to_date(eligibility_end_date,'%m%d%Y'),'%m%d%Y') < (select date_format(str_to_date(fn_header_date_retrieve(122228),'%Y-%m-%d'),'%m%d%Y'))

OR str_to_date(eligibility_end_date,'%m%d%Y') < str_to_date(eligibility_effective_date,'%m%d%Y'))

AND  file_id = 122228;

The code marked in bold will give you incorrect results as the format is '%m%d%Y'.

Please see the results from the query below in newresult and oldresult column:

Invalid dates or dates with space conversion in MySQL