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

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

Solution 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.

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

Solution 3:

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

SQLFiddle example

Solution 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