WHERE Clause to find all records in a specific month
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.
Mysql
- asked 9 years ago
- G John
2Answer
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
- answered 8 years ago
- G John
I find it easier to pass a value as a temporal data type (e.g. DATETIME) then use temporal functionality, specifically DATEADD and DATEPART, to find the start and end dates for the period, in this case the month e.g. this finds the start date and end date pair for the current month, just substitute CURRENT_TIMESTAMP for you parameter of of type DATETIME (note the 1990-01-01 value is entirely arbitrary):
SELECT DATEADD(M,
DATEDIFF(M, '1990-01-01T00:00:00.000', CURRENT_TIMESTAMP),
'1990-01-01T00:00:00.000'),
DATEADD(M,
DATEDIFF(M, '1990-01-01T00:00:00.000', CURRENT_TIMESTAMP),
'1990-01-31T23:59:59.997')
- answered 8 years ago
- B Butts
Your Answer