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

mysql date get
Nurlan picture Nurlan · Aug 4, 2012 · Viewed 107.8k times · Source

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

aleroot picture aleroot · Aug 4, 2012 
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() )

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
How to select date from datetime column?

I have a column of type "datetime" with values like 2009-10-20 10:00:00 I would like to extract date from datetime and write a query like: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Is the …

Read More
how to convert a string to date in mysql?

I have a string column which acts as a date and I want to select it as a date. Is it possible? My sample data format would be; month/day/year -> 12/31/2011

Read More