在 MySQL 中，如何在当月的第一天和当前日期之间进行选择？

How do I select between the 1st day of the current month and current day in MySQL?

提问by Nurlan

I need to select data from MySQL database between the 1st day of the current month and current day.

我需要在当月的第一天和当天之间从 MySQL 数据库中选择数据。

select*from table_name 
where date between "1st day of current month" and "current day"

Can someone provide working example of this query?

有人可以提供此查询的工作示例吗？

回答by aleroot

select * from table_name 
where (date between  DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 30 day), interval 1 day) AND CURDATE() )

Or better :

或更好 ：

select * from table_name 
where (date between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )

回答by Shri

I was looking for a similar query where I needed to use the first day of a month in my query. The last_dayfunction didn't work for me but DAYOFMONTHcame in handy.

我正在寻找一个类似的查询，我需要在查询中使用一个月的第一天。该last_day功能对我不起作用，但DAYOFMONTH派上了用场。

So if anyone is looking for the same issue, the following code returns the date for first day of the current month.

因此，如果有人正在寻找相同的问题，以下代码将返回当月第一天的日期。

SELECT DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY);

Comparing a date column with the first day of the month :

将日期列与月份的第一天进行比较：

select * from table_name where date between 
DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) and CURRENT_DATE

回答by juergen d

select * from table_name 
where `date` between curdate() - dayofmonth(curdate()) + 1
                 and curdate()

SQLFiddle example

SQLFiddle 示例

回答by Ravi Kumar Chandran

I have used the following query. It has worked great for me in the past.

我使用了以下查询。过去它对我很有用。

select date(now()) - interval day(now()) day + interval 1 day

回答by shaku740

try this :

尝试这个 ：

SET @StartDate = DATE_SUB(DATE(NOW()),INTERVAL (DAY(NOW())-1) DAY);
SET @EndDate = ADDDATE(CURDATE(),1);
select * from table where (date >= @StartDate and date < @EndDate);

回答by gregg

select * from <table>
where <dateValue> between last_day(curdate() - interval 1 month + interval 1 day)
                  and curdate();

回答by Enterprise Architect

I found myself here after needing this same query for some Business Intelligence Queries I'm running on an e-commerce store. I wanted to add my solution as it may be helpful to others.

我在电子商务商店中运行的一些商业智能查询需要相同的查询后，我发现自己在这里。我想添加我的解决方案，因为它可能对其他人有帮助。

set @firstOfLastLastMonth = DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL -2 MONTH)),INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL -2 MONTH)))-1 DAY);
set @lastOfLastLastMonth = LAST_DAY(DATE_ADD(NOW(), INTERVAL -2 MONTH));
set @firstOfLastMonth = DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL -1 MONTH)),INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL -1 MONTH)))-1 DAY);
set @lastOfLastMonth = LAST_DAY(DATE_ADD(NOW(), INTERVAL -1 MONTH));
set @firstOfMonth = DATE_ADD(@lastOfLastMonth, INTERVAL 1 DAY);
set @today = CURRENT_DATE;

Today is 2019-10-08 so the output looks like

今天是 2019-10-08 所以输出看起来像

@firstOfLastLastMonth = '2019-08-01'
@lastOfLastLastMonth = '2019-08-31'
@firstOfLastMonth = '2019-09-01'
@lastOfLastMonth = '2019-09-30'
@firstOfMonth = '2019-10-01'
@today = '2019-10-08'

回答by Daniel

select * from table
where date between 
(date_add (CURRENT_DATE, INTERVAL(1 - DAYOFMonth(CURRENT_DATE)) day)) and current_date;

回答by Imran Zahoor

Complete solution for mysql current month and current year, which makes use of indexing properly as well :)

mysql当前月份和当前年份的完整解决方案，它也可以正确使用索引:)

-- Current month
SELECT id, timestampfield 
  FROM table1
 WHERE timestampfield >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY)
   AND timestampfield <=  LAST_DAY(CURRENT_DATE);

-- Current year
SELECT id, timestampfield 
  FROM table1
 WHERE timestampfield >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY)
   AND timestampfield <=  LAST_DAY(CURRENT_DATE);

回答by Ashutosh Srivastava

SELECT date_sub(current_date(),interval dayofmonth(current_date())-1 day) as first_day_of_month;