sql当前月查询

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?

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

You can do so, by using LAST_DAY

SELECT * 
FROM table1 
WHERE 
`DateTime` > LAST_DAY(NOW() - INTERVAL 1 MONTH) 
AND `DateTime` <= LAST_DAY(NOW())

LAST_DAY() Takes a date or datetime value and returns the corresponding value for the last day of the month

As for the case barmar highlighted you can do so

SELECT * 
FROM table1 
WHERE 
`DateTime` > CONCAT(LAST_DAY(NOW() - INTERVAL 1 MONTH),' 23:59:59') 
AND `DateTime` <= LAST_DAY(NOW())

I like to write it this way:

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

If you mean the current calendar month (so all records where DateTime month is same as current month), you can use the MONTH function and YEARfunction to extract those values:

SELECT * FROM table1 
WHERE MONTH(DateTime) = MONTH(NOW()) AND YEAR(DateTime) = YEAR(NOW())

set @YM=concat(year(now()),'-',month(now()));

select
    *
from
    table1
where
    concat(year(DateTime),'-',month(DateTime))=@YM;

It's not elegant but it'll work