mysql 日期与 date_format 的比较

2021-11-20 00:00:00 date mysql date-format

我用谷歌搜索并尝试了几种比较日期的方法，但不幸的是没有得到预期的结果.我有如下记录的当前状态:

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                              |

我认为结果不应该包括28-10-2012".有什么建议吗?提前致谢.

I believe that the result should not include '28-10-2012'. Any suggestion? Thanks in advance.

推荐答案

您的格式从根本上来说不是一个可排序的格式 - 您正在比较 字符串 和字符串28-10-2012" 大于02-11-2012".

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.

试试这个:

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

请注意，如果 starttime 是 DATETIME 字段，您可能需要考虑更改查询以避免重复转换.(优化器很可能足够聪明来避免它，但值得检查.)

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';

(请注意，开始时将日期格式化为 dmY 是不寻常的 - 通常最好使用 yMd，即 ISO-8601 标准等.但是，上面的代码可以满足您在问题中的要求.)

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

相关文章