Select records from current month

I have a table with a varchar field called slldate. This field contains dates in this format:

2010-08-30

(YYYY-MM-DD)

Now I would like to select the records containing the current month using a开发者_运维百科 mysql query.

Anyone?

as you used a char field instead a date field, you have to cast the value and then use the normal date functions. like

SELECT * FROM table WHERE MONTH(CAST(slidate as date)) = MONTH(NOW()) AND YEAR(CAST(slidate as date)) = YEAR(NOW())

Try this:

SELECT * FROM table_name WHERE MONTH(slldate) = date('m') AND YEAR(slldate) = date('Y');

I have a table with a varchar field called slldate. This field contains dates

Then you should change it to a date field. You (should) know it's the right thing to do.

The method below will work with your varchar strings - but can be simplified somewhat for dates.

Note that since your date strings are already big-endian, you don't need to cast them and loose the benefit of index optinmisation:

 SELECT *
 FROM yourtable
 WHERE slldate >= CONCAT(DATE_FORMAT('%Y-%m-', NOW()),'01')
 AND slldate < CONCAT(DATE_FORMAT('%Y-%m-', NOW() + INTERVAL 1 MONTH), '01')

SELECT * FROM table_name WHERE YEAR(savedate) = YEAR(CURDATE()) AND MONTH(savedate) = MONTH(CURDATE())