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

Question

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

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

Can someone provide working example of this query?

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

Answer 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