与date_format进行mysql日期比较 [英] mysql date comparison with date_format
问题描述
mysql>从数据中选择date_format(date(starttime),'%d-%m-%Y');
+ ----------------------------------------- +
| 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 |
我想比较日期,因此这样做:
mysql>从数据中选择date_format(date(starttime),'%d-%m-%Y'),其中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 |
我相信结果不应该包含28 -10-2012。任何建议?感谢您提前。
您的格式从根本上不是一个可以开始的排序 - 您正在比较 / em>,字符串28-10-2012 大于02-11-2012。
相反,你应该将日期作为日期进行比较,然后只将其转换为目标格式输出。
尝试这样:
从数据
中选择date_format(date(starttime),'%d-%m-%Y')其中date(starttime)> = date'2012-11-02';
(输入必须始终为year-month-value表单,根据文档。)
请注意,如果 starttime
是一个 DATETIME
字段,则可能需要考虑更改查询以避免重复转换。 (优化器可能很聪明,可以避免它,但值得检查。)
select date_format(date(starttime) '%d-%m-%Y')from data
where starttime> ='2012-11-02 00:00:00';
(请注意,将日期格式化为 dmY
开始 - 一般来说,最好使用 yMd
,作为ISO-8601标准等。但是,上面的代码在你所要求的问题。)
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.)
这篇关于与date_format进行mysql日期比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!