MySQL SQL - 仅获取当前年份的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27745475/
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
SQL - Get result of current year only
提问by Md. Motiur Rahman
How can I get the result of the current year using SQL?
如何使用 SQL 获取当前年份的结果？
I have a table that has a column date with the format yyyy-mm-dd.
我有一个表，它的日期列格式为yyyy-mm-dd。
Now, I want to do select query that only returns the current year result.
现在，我想做只返回当前年份结果的选择查询。
The pseudo code should be like:
伪代码应该是这样的：
select * from table where date is (current year dates)
The result should be as following:
结果应如下所示：
id date
2 2015-01-01
3 2015-02-01
9 2015-01-01
6 2015-02-01
How can I do this?
我怎样才能做到这一点？
回答by John Conde
回答by axiac
Using WHERE YEAR(date) = YEAR(CURDATE())is correct but it cannot use an index on column dateif exists; if it doesn't exist it should.
使用WHERE YEAR(date) = YEAR(CURDATE())是正确的，但date如果存在，则不能在列上使用索引；如果它不存在它应该。
A better solution is:
更好的解决方案是：
SELECT *
FROM tbl
WHERE `date` BETWEEN '2015-01-01' AND '2015-12-31'
The dates (first and last day of the year) need to be generated from the client code.
日期（一年的第一天和最后一天）需要从客户端代码生成。
回答by jmrpink
When I tried these answers on SQL server, I got an error saying curdate() was not a recognized function.
当我在 SQL 服务器上尝试这些答案时，我收到一条错误消息，指出 curdate() 不是一个可识别的函数。
If you get the same error, using getdate() instead of curdate() should work!
如果您遇到相同的错误，使用 getdate() 而不是 curdate() 应该可以工作！
回答by Adil Ayoub
--========= Get Current Year ===========
--========== 获取当前年份 ============
Select DATEPART(yyyy, GETDATE())
回答by EngineerCoder
SELECT
date
FROM
TABLE
WHERE
YEAR (date) = YEAR (CURDATE());
回答by hakiko
SELECT id, date FROM your_table WHERE YEAR( date ) = YEAR( CURDATE() )
回答by Faisal
You can do this using SQL DATE_FORMATE(). like below:
您可以使用 SQL 执行此操作DATE_FORMATE()。像下面这样：
SELECT
date
FROM
TABLE
WHERE
DATE_FORMAT(date, '%Y') = YEAR (CURDATE());
回答by randyh22
If the date field contains a time component, you want to include December 31 so you have to go to January 1 of the next year. You also don't have to use code to insert dates into the SQL. You can use the following
如果日期字段包含时间组件，您希望包括 12 月 31 日，因此您必须转到下一年的 1 月 1 日。您也不必使用代码将日期插入到 SQL 中。您可以使用以下
SELECT * FROM table
WHERE date BETWEEN MAKEDATE(YEAR(CURDATE()), 1) AND MAKEDATE(YEAR(CURDATE())+1, 1)
This will give you January 1st of the current year through January 1st at midnight of the following year.
这将使您从当年的 1 月 1 日到下一年的 1 月 1 日午夜。
As @Clockwork-Muse pointed out, if the date field does not contain a time component, you would want to exclude January 1 of the following year by using
正如@Clockwork-Muse 指出的那样，如果日期字段不包含时间组件，则您需要使用以下方法排除下一年的 1 月 1 日
WHERE date >= MAKEDATE(YEAR(CURDATE()), 1) AND date < MAKEDATE(YEAR(CURDATE())+1, 1)
