Mubin Mubin - 4 years ago 110
SQL Question

Filter data by date?

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 Source

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'
;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download