我如何获得本月的第一天?

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

我需要从MySQL数据库中选择本月1日到今日的数据。

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

Can someone provide working example of this query?

有人能提供这个查询的工作示例吗?

11 个解决方案

#1

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() )

#2

I was looking for a similar query where I needed to use the first day of a month in my query. The last_day function didn't work for me but DAYOFMONTH came 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

#3

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

SQLFiddle example

SQLFiddle例子

#4

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

#5

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);

#6

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

#7

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);

#8

A less orthodox approach might be

一个不太正统的方法可能是。

SELECT * FROM table_name 
WHERE LEFT(table_name.date, 7) = LEFT(CURDATE(), 7)
  AND table_name.date <= CURDATE();

as a date being between the first of a month and now is equivalent to a date being in this month, and before now. I do feel that this is a bit easier on the eyes than some other approaches, though.

一个月的第一天到现在的日期等于这个月的日期，也就是现在之前的日期。不过，我确实觉得这对眼睛来说比其他方法要容易一些。

#9

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

#10

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

#11

SET @date:='2012-07-11';
SELECT date_add(date_add(LAST_DAY(@date),interval 1 DAY), 
       interval -1 MONTH) AS first_day