MySQL MySQL选择带有日期的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12216033/
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
MySQL select rows with date like
提问by Axxess
In MySQL I have this query
在 MySQL 我有这个查询
SELECT DISTINCT date, descr FROM book ORDER BY date
Date is in format yyyy-mm-dd
日期是格式 yyyy-mm-dd
I want to select only the the books from January 2012. I have tried to use likebut that does not work.
我只想选择2012 年 1 月的书籍。我试过使用，like但这不起作用。
Any ideas?
有任何想法吗？
回答by hims056
Using DATE_FORMATfunction
使用DATE_FORMAT功能
SELECT DISTINCT date, descr FROM book
WHERE DATE_FORMAT(date, '%Y %m') = DATE_FORMAT('2012-01-01', '%Y %m')
ORDER BY date
Or using MONTHand YEARfunctions
SELECT DISTINCT date, descr FROM book
WHERE Month(date) = Month('2012-01-01')
AND Year(date) = Year('2012-01-01')
ORDER BY date;
Or using BETWEENfunctions
或者使用BETWEEN函数
SELECT DISTINCT date, descr FROM book
WHERE date BETWEEN '2012-01-01'
AND '2012-01-31'
ORDER BY date;
SELECT DISTINCT date, descr FROM book
WHERE date >= '2012-01-01'
AND date <= '2012-01-31'
ORDER BY date;
See this SQLFiddle
看到这个 SQLFiddle
回答by mtk
You can use >=and <=operators here. Check the below code:
您可以在此处使用>=和<=运算符。检查以下代码：
SELECT *
FROM book
WHERE date >= '2012-01-01' AND date <= '2012-01-31'
回答by j0nes
Try this:
尝试这个：
SELECT DISTINCT date, descr FROM book WHERE YEAR(date) = '2012' and MONTH(date) = '1'
This works if your "date"-column is a MySQL date field.
如果您的“日期”列是 MySQL 日期字段，则此方法有效。
回答by Rolf
If you are adamant that you want to use the LIKE syntax, you can convert the date to CHAR first:
如果您坚持要使用 LIKE 语法，则可以先将日期转换为 CHAR：
SELECT DISTINCT date, descr FROM book WHERE CAST(date AS char) LIKE '2012-01%' ORDER BY date;
SELECT DISTINCT date, descr FROM book WHERE CAST(date AS char) LIKE '2012-01%' ORDER BY date;
回答by Ferran
Using like also works. With @hims056 fiddle, you can test it:
使用 like 也可以。使用@hims056 fiddle，您可以对其进行测试：
SELECT DISTINCT ID, date FROM book
WHERE date LIKE '2012-01%'
ORDER BY date;
However, it's not usual to use a like for date filtering, for me it's more natural to use >= and <= , or between. Also, there's a performance benefit.
但是，使用 like 进行日期过滤并不常见，对我来说使用 >= 和 <= 或两者之间更自然。此外，还有性能优势。
回答by Muhammad Raheel
SELECT DISTINCT date, descr
FROM book
WHERE YEAR = DATE(NOW()) AND MONTH(date) = '1'
This will give you this years books
这会给你今年的书
