比较 MySQL 中的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3651985/
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
Compare dates in MySQL
提问by NVG
I want to compare a date from a database that is between 2 given dates. The column from the database is DATETIME, and I want to compare it only to the date format, not the datetime format.
我想比较数据库中两个给定日期之间的日期。数据库中的列是 DATETIME，我只想将它与日期格式进行比较，而不是日期时间格式。
SELECT * FROM `players` WHERE CONVERT(CHAR(10),us_reg_date,120) >= '2000-07-05' AND CONVERT(CHAR(10),us_reg_date,120) <= '2011-11-10'
I get this error when I execute the SQL above:
执行上面的 SQL 时出现此错误：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'us_reg_date,120) >= '2000-07-05' AND CONVERT(CHAR(10),us_reg_date,120) <= '2011-' at line 1
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在 'us_reg_date,120) >= '2000-07-05' AND CONVERT(CHAR(10),us_reg_date,120) <= '2011-' 附近使用的正确语法1号线
How can this problem be fixed?
如何解决这个问题？
回答by Nik
You can try below query,
您可以尝试以下查询，
select * from players
where
us_reg_date between '2000-07-05'
and
DATE_ADD('2011-11-10',INTERVAL 1 DAY)
回答by Mark Byers
That is SQL Server syntaxfor converting a date to a string. In MySQL you can use the DATEfunction to extract the date from a datetime:
这是用于将日期转换为字符串的SQL Server 语法。在 MySQL 中，您可以使用DATE函数从日期时间中提取日期：
SELECT *
FROM players
WHERE DATE(us_reg_date) BETWEEN '2000-07-05' AND '2011-11-10'
But if you want to take advantage of an index on the column us_reg_dateyou might want to try this instead:
但是如果你想利用列上的索引，us_reg_date你可能想试试这个：
SELECT *
FROM players
WHERE us_reg_date >= '2000-07-05'
AND us_reg_date < '2011-11-10' + interval 1 day
回答by Rahatur
This works for me:
这对我有用：
select date_format(date(starttime),'%Y-%m-%d') from data
where date(starttime) >= date '2012-11-02';
Note the format string '%Y-%m-%d' and the format of the input date.
注意格式字符串 '%Y-%m-%d' 和输入日期的格式。
回答by Gurjeet Singh
hey guys thank you for your help i got the answer.
嘿伙计们谢谢你的帮助我得到了答案。
here is the code.......
这是代码......
SELECT * FROM table
WHERE STR_TO_DATE(column, '%d/%m/%Y')
BETWEEN STR_TO_DATE('29/01/15', '%d/%m/%Y')
AND STR_TO_DATE('07/10/15', '%d/%m/%Y')
回答by Iria
this is what it worked for me:
这对我有用：
select * from table
where column
BETWEEN STR_TO_DATE('29/01/15', '%d/%m/%Y')
AND STR_TO_DATE('07/10/15', '%d/%m/%Y')
Please, note that I had to change STR_TO_DATE(column, '%d/%m/%Y') from previous solutions, as it was taking ages to load
请注意，我必须从以前的解决方案中更改 STR_TO_DATE(column, '%d/%m/%Y')，因为加载需要很长时间
