MySQL 如何在mysql中获得每个对应月份的第一天？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3298288/
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
How to get first day of every corresponding month in mysql?
提问by David
I want to get first day of every corresponding month of current year. For example, if user selects '2010-06-15', query demands to run from '2010-06-01' instead of '2010-06-15'.
我想获得当年每个相应月份的第一天。例如，如果用户选择“2010-06-15”，则查询要求从“2010-06-01”而不是“2010-06-15”运行。
Please help me how to calculate first day from selected date. Currently, I am trying to get desirable using following mysql select query:
请帮助我如何计算所选日期的第一天。目前，我正在尝试使用以下 mysql 选择查询来获得理想的效果：
Select
DAYOFMONTH(hrm_attendanceregister.Date) >=
DAYOFMONTH(
DATE_SUB('2010-07-17', INTERVAL - DAYOFMONTH('2010-07-17') + 1 DAY
)
FROM
hrm_attendanceregister;
Thanks
谢谢
回答by Krunal
Is this what you are looking for:
这是你想要的：
select CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE);
回答by Stéphane
You can use the LAST_DAYfunction provided by MySQL to retrieve the last day of any month, that's easy:
您可以使用MySQL 提供的LAST_DAY函数来检索任何月份的最后一天，这很简单：
SELECT LAST_DAY('2010-06-15');
Will return:
将返回：
2010-06-30
Unfortunately, MySQL does not provide any FIRST_DAYfunction to retrieve the first day of a month (not sure why). But given the last day, you can add a day and subtract a month to get the first day. Thus you can define a custom function:
不幸的是，MySQL 没有提供任何FIRST_DAY函数来检索一个月的第一天（不知道为什么）。但是考虑到最后一天，你可以加一天减一个月得到第一天。因此你可以定义一个自定义函数：
DELIMITER ;;
CREATE FUNCTION FIRST_DAY(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
RETURN ADDDATE(LAST_DAY(SUBDATE(day, INTERVAL 1 MONTH)), 1);
END;;
DELIMITER ;
That way:
那样：
SELECT FIRST_DAY('2010-06-15');
Will return:
将返回：
2010-06-01
回答by Pacerier
There is actually a straightforward solution since the first day of the month is simply today - (day_of_month_in_today - 1):
实际上有一个简单的解决方案，因为一个月的第一天很简单today - (day_of_month_in_today - 1)：
select now() - interval (day(now())-1) day
Contrast that with the the other methodswhich are extremely roundabout and indirect.
Also, since we are not interested in the time component, curdate()is a better (and faster) function than now(). We can also take advantage of subdate()'s 2-arity overload since that is more performant than using interval. So a better solution is:
此外，由于我们对时间分量不感兴趣，因此curdate()是比 更好（更快）的函数now()。我们还可以利用subdate()2 元重载的优势， 因为它比使用interval. 所以更好的解决办法是：
select subdate(curdate(), (day(curdate())-1))
回答by Zordon
This is old but this might be helpful for new human web crawlers XD
这是旧的，但这可能对新的人类网络爬虫有帮助 XD
For the first day of the current month you can use:
对于当月的第一天，您可以使用：
SELECT LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY;
回答by Andrew Vit
You can use EXTRACTto get the date parts you want:
您可以使用EXTRACT来获取您想要的日期部分：
EXTRACT( YEAR_MONTH FROM DATE('2011-09-28') )
-- 201109
This works well for grouping.
这适用于分组。
回答by Thomas
I'm surprised no one has proposed something akin to this (I do not know how performant it is):
我很惊讶没有人提出类似的东西（我不知道它的性能如何）：
CONCAT_WS('-', YEAR(CURDATE()), MONTH(CURDATE()), '1')
Additional date operations could be performed to remove formatting, if necessary
如有必要，可以执行其他日期操作以删除格式
回答by Jesús Correa
You can use DATE_FORMAT() function in order to get the first day of any date field.
您可以使用 DATE_FORMAT() 函数来获取任何日期字段的第一天。
SELECT DATE_FORMAT(CURDATE(),'%Y-%m-01') as FIRST_DAY_CURRENT_MONTH
FROM dual;
Change Curdate() with any other Date field like:
使用任何其他日期字段更改 Curdate()，例如：
SELECT DATE_FORMAT(purchase_date,'%Y-%m-01') AS FIRST_DAY_SALES_MONTH
FROM Company.Sales;
Then, using your own question:
然后，使用您自己的问题：
SELECT *
FROM
hrm_attendanceregister
WHERE
hrm_attendanceregister.Date) >=
DATE_FORMAT(CURDATE(),'%Y-%m-01')
You can change CURDATE() with any other given date.
您可以使用任何其他给定日期更改 CURDATE()。
回答by Salil
use date_formatmethod and check just month & year
使用date_format方法并检查月份和年份
select * from table_name where date_format(date_column, "%Y-%m")="2010-06"
回答by Ajay749
This works fine for me.
这对我来说很好用。
date(SUBDATE("Added Time", INTERVAL (day("Added Time") -1) day))
** replace "Added Time" with column name
**用列名替换“添加时间”
Use Cases:
用例：
If you want to reset all date fields except Monthand Year.
If you want to retain the column formatas "date". (not as "text" or "number")
如果要重置除Month和Year之外的所有日期字段。
如果要将列格式保留为“日期”。（不是“文本”或“数字”）
回答by Frank
Slow (17s):
慢（17 秒）：
SELECT BENCHMARK(100000000, current_date - INTERVAL (day(current_date) - 1) DAY);
SELECT BENCHMARK(100000000, cast(DATE_FORMAT(current_date, '%Y-%m-01') as date));
If you don't need a date type this is faster: Fast (6s):
如果您不需要日期类型，这会更快：Fast (6s)：
SELECT BENCHMARK(100000000, DATE_FORMAT(CURDATE(), '%Y-%m-01'));
SELECT BENCHMARK(100000000, DATE_FORMAT(current_date, '%Y-%m-01'));
