MySQL 选择年和月的第一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18412017/
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 First Day of Year and Month
提问by Mark Bogner
How to find first day of year in SELECT?
如何在SELECT中找到一年的第一天？
SELECT `DATE`,`SomeValue1`,`SomeValue2`
FROM `SomeTableName`
WHERE (`DATE` >= [...first day of the year in date format...])
I found this for month - but I don't QUITE have the grasp enough for year: (I was looking for this for a separate query to find data between beginning of month and now)
我找到了这个月 - 但我对一年的掌握还不够：（我正在寻找这个单独的查询来查找月初和现在之间的数据）
WHERE (`DATE` between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )
回答by Grim...
I think you need:
我认为你需要：
WHERE (`DATE` between DATE_FORMAT(NOW() ,'%Y-01-01') AND NOW() )
To be honest, you could do:
老实说，你可以这样做：
WHERE (`DATE` between DATE_FORMAT(NOW() ,'%Y') AND NOW() )
回答by Tim Bray
To get the first day of the current year, I use:
要获得当年的第一天，我使用：
SELECT MAKEDATE(year(now()),1);
So in your query you would write:
所以在你的查询中你会写：
where `date` >= MAKEDATE(year(now()),1)
I quite commonly do something like a sales report for the past full 2 years, but I always want to start at the beginning of a year. So shows 2 full years and the year to date.
我通常会做类似过去整整两年的销售报告之类的事情，但我总是想从年初开始。所以显示 2 整年和今年迄今为止。
where date>= MAKEDATE(year(now()-interval 2 year),1)
But to further complicate it, our financial years starts on the first of May. I always want to start on the first of May.
但更复杂的是，我们的财政年度从五月一日开始。我总是想从五月一日开始。
where date >= MAKEDATE(year(now()-interval 2 year),1) + interval 120 day
or as an alternative
或作为替代
where date >= MAKEDATE(year(now()-interval 2 year),121)
The first of May being the 121st day of a the year. But this method does not work in leap years.
五月一日是一年中的第 121 天。但是这种方法在闰年不起作用。
The leap year proof version is:
闰年证明版本是：
where date => select MAKEDATE(year(now()-interval 5 year),1) + interval 4 month
Which will always return a xxxx-05-01 date, whether a leap year or not.
这将始终返回 xxxx-05-01 日期，无论是否为闰年。
回答by Gordon Linoff
If the above works for the month, then this will work for the year:
如果上述适用于本月，则适用于本年：
WHERE (`DATE` between DATE_FORMAT(NOW() ,'%Y-01-01') AND NOW() )
