Output all records for this month?

[7724]

Hello All,

Looking for some help. I was hoping to run a query to get all records in a table where the current month is equal to a column called start.

So for example this month is April. So say in my database I had these records:

2009-04-01 09:00:00
2009-03-08 09:00:00
2009-04-15 09:00:00

Only the first two records would be outputted. I thought it would be something like this?

SELECT * FROM `table` WHERE (MONTH(starts) = MONTH(GETDATE()))

Thanks

[Mandes]

Im not sure that GETDATE() is valid MySQL (I think its a MSSQL command)

Try using NOW()

SELECT * 
  FROM `table` 
    WHERE MONTH(starts) = MONTH(NOW())
;

also your text said you field was called 'start' but you reference 'starts' in the code

[r937]

using MONTH like that will return all april rows, regardless of the year

you'd have to use both YEAR and MONTH to get it right

there's a better, way, however, and this way will also scale, i.e. if there's an index on the date column, the following method will be efficient too --

WHERE starts >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
  AND starts  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY 
                + INTERVAL 1 MONTH