I googled and tried several ways to compare date but unfortunately didn't get the result as expected. I have current state of records like following:
mysql> select date_format(date(starttime),'%d-%m-%Y') from data;
+-----------------------------------------+
| date_format(date(starttime),'%d-%m-%Y') |
+-----------------------------------------+
| 28-10-2012 |
| 02-11-2012 |
| 02-11-2012 |
| 02-11-2012 |
| 03-11-2012 |
| 03-11-2012 |
| 07-11-2012 |
| 07-11-2012 |
I would like to compare date and therefore do like this:
mysql> select date_format(date(starttime),'%d-%m-%Y') from data where date_format(date(starttime),'%d-%m-%y') >= '02-11-2012';
+-----------------------------------------+
| date_format(date(starttime),'%d-%m-%Y') |
+-----------------------------------------+
| 28-10-2012 |
| 02-11-2012 |
| 02-11-2012 |
| 02-11-2012 |
| 03-11-2012 |
| 03-11-2012 |
| 07-11-2012 |
| 07-11-2012 |
I believe that the result should not include '28-10-2012'. Any suggestion? Thanks in advance.
解决方案
Your format is fundamentally not a sortable one to start with - you're comparing strings, and the string "28-10-2012" is greater than "02-11-2012".
Instead, you should be comparing dates as dates, and then only converting them into your target format for output.
Try this:
select date_format(date(starttime),'%d-%m-%Y') from data
where date(starttime) >= date '2012-11-02';
(The input must always be in year-month-value form, as per the documentation.)
Note that if starttime is a DATETIME field, you might want to consider changing the query to avoid repeated conversion. (The optimizer may well be smart enough to avoid it, but it's worth checking.)
select date_format(date(starttime),'%d-%m-%Y') from data
where starttime >= '2012-11-02 00:00:00';
(Note that it's unusual to format a date as d-m-Y to start with - it would be better to use y-M-d in general, being the ISO-8601 standard etc. However, the above code does what you asked for in the question.)