Search code examples
mysqlsqldatecomparison

mysql compare specific date

I have a table which contains a varchar field containing date like '15 May 2015 - 03:10 am'

I have to compare all date in this table with the current date to retrieve row which are next the current date and row which are recent to the current date

I found this solution here:

select * 
  from reunion a 
 where STR_TO_DATE(a.dateDebut,'%d %b %Y - %I:%i %p')>  
                 DATE_FORMAT(NOW(),'%d %b %Y - %I:%i %p');

but it returns all date: '15 May 2013 - 03:10 AM' ,'15 May 2015 - 03:10 am','15 May 2016 - 03:10 am'

and when I change to "<" it returns 0 rows;

is there any other solution to give the varchar field the ability to be a valid date?

Solution

  • Your query is overkill. You just need to convert your date format to a date. now() is already a date. So try:

    select *
from reunion a
where STR_TO_DATE(a.dateDebut,'%d %b %Y - %I:%i %p') > NOW();