SELECT * FROM `table` WHERE `fr` < '07/2002'
- use str_to_date to convert the start of month values to dates and compare
select * from `table` where str_to_date(concat('01/', fr), '%d/%m/%Y') < str_to_date('01/07/2002', '%d/%m/%Y') ;
+---------+ | fr | +---------+ | 04/2000 | +---------+
while the above is a solution to the question as asked, it is just dealing with the symptoms not the underlying issue.
the real issue is the storage type being used to store date information consider using actual dates to store this information. this causes the following symptoms :
- complexity : we have to perform further manipulations to transform into the date type we can use
- performance ( see above )
- maintenance ( see this question )
we can instead fix this issue where it is caused by changing the storage type to correctly reflect the semantic contents ( its date information and should be able to be compared in this way simply )
alter table `table` add column fr_datetype date not null; update `table` set fr_datetype = str_to_date(concat('01/', fr), '%d/%m/%Y') ; -- test conversion to date type select 'error converting' as test_conversion from `table` where concat(lpad(extract(month from fr_datetype), 2, '0'), '/', extract(year from fr_datetype)) <> fr ; -- only finalise this based on successful completion of above select ( no rows returned ) alter table `table` drop column fr; alter table `table` change column fr_datetype fr date not null;
select * from `table` where fr < '2002-07-01' ;