MySQL 在SQL中如何比较日期值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/318571/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In SQL how to compare date values?
提问by fmsf
Using MySQL syntax and having a table with a row like:
使用 MySQL 语法并有一个包含如下行的表：
mydate DATETIME NULL,
Is there a way to do something like:
有没有办法做这样的事情：
... WHERE mydate<='2008-11-25';
I'm trying but not really getting it to work.
我正在尝试，但并没有真正让它发挥作用。
采纳答案by fmsf
Nevermind found an answer. Ty the same for anyone who was willing to reply.
没关系找到答案。对于任何愿意回答的人来说也是如此。
WHERE DATEDIFF(mydata,'2008-11-20') >=0;
回答by Eli
Uh, WHERE mydate<='2008-11-25'isthe way to do it. That should work.
嗯，WHERE mydate<='2008-11-25'是这样做的方法。那应该工作。
Do you get an error message? Are you using an ancient version of MySQL?
您是否收到错误消息？您使用的是旧版本的 MySQL 吗？
Edit: The following works fine for me on MySQL 5.x
编辑：以下在 MySQL 5.x 上对我来说很好用
create temporary table foo(d datetime);
insert into foo(d) VALUES ('2000-01-01');
insert into foo(d) VALUES ('2001-01-01');
select * from foo where d <= '2000-06-01';
回答by Jonathan Leffler
In standard SQL syntax, you would use:
在标准 SQL 语法中，您将使用：
WHERE mydate <= DATE '2008-11-20'
That is, the keyword DATE should precede the string. In some DBMS, however, you don't need to be that explicit; the system will convert the DATE column into a string, or the string into a DATE value, automatically. There are nominally some interesting implications if the DATE is converted into a string - if you happen to have dates in the first millennium (0001-01-01 .. 0999-12-31) and the leading zero(es) are omitted by the formatting system.
也就是说，关键字 DATE 应该在字符串之前。但是，在某些 DBMS 中，您不需要那么明确；系统会自动将 DATE 列转换为字符串，或将字符串转换为 DATE 值。如果将 DATE 转换为字符串，名义上会有一些有趣的含义 - 如果您碰巧在第一个千年 (0001-01-01 .. 0999-12-31) 中有日期并且前导零被省略格式化系统。
回答by too much php
You could add the time component
您可以添加时间组件
WHERE mydate<='2008-11-25 23:59:59'
but that might fail on DST switchover dates if mydate is '2008-11-25 24:59:59', so it's probably safest to grab everything before the next date:
但是，如果 mydate 是“2008-11-25 24:59:59”，则在 DST 切换日期可能会失败，因此在下一个日期之前获取所有内容可能是最安全的：
WHERE mydate < '2008-11-26 00:00:00'
回答by DancingFool
Your problem may be that you are dealing with DATETIME data, not just dates. If a row has a mydate that is '2008-11-25 09:30 AM', then your WHERE mydate<='2008-11-25';is not going to return that row. '2008-11-25' has an implied time of 00:00 (midnight), so even though the date part is the same, they are not equal, and mydate is larger.
您的问题可能是您正在处理 DATETIME 数据，而不仅仅是日期。如果一行的 mydate 是 '2008-11-25 09:30 AM'，那么你的WHERE mydate<='2008-11-25'; 不会返回该行。'2008-11-25' 的隐含时间为 00:00（午夜），因此即使日期部分相同，它们也不相等，并且 mydate 更大。
If you use < '2008-11-26' instead of <= '2008-11-25', that would work. The Datediff method works because it compares just the date portion, and ignores the times.
如果您使用 < '2008-11-26' 而不是 <= '2008-11-25'，那会起作用。Datediff 方法有效，因为它只比较日期部分，而忽略时间。
