sql当前月查询

Question

I'd like to show records of the current month May 2014, I'm trying to pull a record with the following query

SELECT * FROM table1 WHERE DateTime > (CURDATE() - INTERVAL 1 MONTH)

tried the query on display records using php and query it on mysql (phpmyadmin) but yields wrong records because it shows the last month's record, which is april

Maybe the query is wrong, any ideas? suggestions?

Answer 1

If the current date is May 7, then subtracting 1 month is April 7, and your query returns all rows since then. You need to start from the first date of the current month.

SELECT *
FROM table1
WHERE DateTime >= DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00');