mysql 比较日期格式化,与date_format进行mysql日期比较

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.)