WHERE Clause to find all records in a specific month

Question

I want to be able to give a stored procedure a Month and Year and have it return everything that happens in that month, how do I do this as I can't compare between as some months have different numbers of days etc?

What's the best way to do this? Can I just ask to compare based on the year and month?

Thanks.

Answer 1

I think the function you're looking for is MONTH(date). You'll probably want to use 'YEAR' too.

Let's assume you have a table named things that looks something like this:

id happend_at -- ---------------- 1  2009-01-01 12:08 2  2009-02-01 12:00 3  2009-01-12 09:40 4  2009-01-29 17:55 

And let's say you want to execute to find all the records that have a happened_at during the month 2009/01 (January 2009). The SQL query would be:

SELECT id FROM things     WHERE MONTH(happened_at) = 1 AND YEAR(happened_at) = 2009 

Which would return:

id --- 1 3 4