Comparing dates in sql Phpmyadmin

Question

i am trying to run a query in mysql to select all records from a table where the "end_date" of that record is greater than or equal to the current date, but it keeps coming up with "MySQL returned an empty result set". But such a record definitely exists n my table, is there a problem with my query?

this is the value in the end_date column for the one record "03/24/2014".

and this is my query

SELECT * FROM ******** WHERE DATE(end_date) > DATE(CURDATE())

I have my suspicions however that it may be the date format meaning the date format on my computer might be like this: dd/MM/yyyy Whereas the date format on the server might be like this : MM/dd/yyyy

Unfortunately if that is the case i dunno what to do about that yet.

Uhmm...i dunno i thought it would be easier saving and retrieving the dates from php to the database that way Was i wrong? — Blank EDjok
– Blank EDjok, Commented Mar 12, 2014 at 17:57
Oho!! Thank you, i changed that and it worked, i feel really silly though. :) — Blank EDjok
– Blank EDjok, Commented Mar 12, 2014 at 18:01

Ray · Accepted Answer · 2014-03-12 18:07:08Z

2

As noted in comment by @FreshPrinceOfSO, better to store date as real DATE, TIMESTAMP, or DATETIME.

If that's not an option--it's not your db to alter for example--then Mysql has a STR_TO_DATE() function you can use:

      SELECT * FROM ******** 
          WHERE STR_TO_DATE(end_date,'%m/%d/%Y')  > CURDATE();

HOWEVER... If end_date column has an index, the function applied to it will nullify the index benefit.

edited Mar 12, 2014 at 18:07

answered Mar 12, 2014 at 18:01

Ray

41.6k22 gold badges110 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comparing dates in sql Phpmyadmin

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related