Filter data by date?

Question

I've column that contains data like so,

07/2002
05/2005
04/2000

month/year

can I filter out data using query, i.e

SELECT * FROM `table` WHERE `fr` < '07/2002'

it should return 04/2000

is't possible using MySQL or I've to use other language to select and filter out data like PHP?

Answer 1

plan

• use str_to_date to convert the start of month values to dates and compare

---

query

select *
from `table`
where str_to_date(concat('01/', fr), '%d/%m/%Y') 
      < 
      str_to_date('01/07/2002', '%d/%m/%Y')
;

output

+---------+
| fr      |
+---------+
| 04/2000 |
+---------+

sqlfiddle

---

note

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 :

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 )

fix

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;

simplified solution

select *
from `table`
where fr < '2002-07-01'
;