0

How do I compare date in the format dd-mon-yy? For example: 10-NOV-14 > 07-OCT-13.

select expiration_date from grocery where expiration_date < 14-oct-13
Brian
  • 5,069
  • 7
  • 37
  • 47
Yachee
  • 1
  • 1
  • 1
  • 3
  • What is the data type of the `expiration_date` column? The answer will vary depending on the data type. – Ed Gibbs Oct 14 '13 at 18:09
  • 3
    You should be storing the dates in MySQL's default YYYY-MM-DD date format. It makes life *much* easier. – John Conde Oct 14 '13 at 18:09
  • Possible duplicate of: [this question](http://stackoverflow.com/questions/13507642/mysql-date-comparison-with-date-format). – Brian Oct 14 '13 at 18:10
  • You should use MySQL's date format and not your own: `SELECT expiration_date FROM grocery WHERE expiration_date < '2013-10-13';` When using the date field, you can also do things like `WHERE expiration_date < now() - interval 1 month`. See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html – Hamza Kubba Oct 14 '13 at 18:12
  • it is in the format dd-mon-yy for example 10-NOV-14 – Yachee Oct 14 '13 at 18:16
  • Yachee - when you do `desc grocery`, what data type shows for `expiration_date`? `DATE`? `DATETIME`? `TIMESTAMP`? `CHAR`? `VARCHAR`? – Ed Gibbs Oct 14 '13 at 19:10

1 Answers1

1

Use STR_TO_DATE:

select expiration_date from grocery
where expiration_date < STR_TO_DATE('14-oct-13', '%d-%b-%y')

expiration_date will also need to be wrapped in STR_TO_DATE if it is not already a DATE format.

Alex Wittig
  • 2,800
  • 1
  • 33
  • 42