Problem
I want to be able to pass a Month and Year to a stored procedure and have it return everything that happened in that month. How do I accomplish this because various months have varying numbers of days and so on?
What is the most effective method for accomplishing this? Could I simply request a comparison depending on the year and month?
Thanks.
Asked by Tarks
Solution #1
MONTH appears to be the function you’re looking for (date). You’ll almost certainly want to use ‘YEAR’ as well.
Let’s pretend you’ve got a table called things that looks 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
Let’s say you wish to find all the entries with a happened at throughout the month of January 2009. (January 2009). The SQL query would be as follows:
SELECT id FROM things
WHERE MONTH(happened_at) = 1 AND YEAR(happened_at) = 2009
Which would return:
id
---
1
3
4
Answered by Shalom Craimer
Solution #2
Using the MONTH and YEAR functions, as most of the comments recommend, has the drawback of SQL Server being unable to use any indexes on your date field. On a huge table, this can be disastrous.
I’d suggest passing a DATETIME value to the stored procedure (e.g. @StartDate) that indicates the first day of the month you’re interested in.
After then, you can make use of
SELECT ... FROM ...
WHERE DateColumn >= @StartDate
AND DateColumn < DATEADD(month, 1, @StartDate)
If you need to pass the month and year as separate arguments to the stored procedure, use CAST and CONVERT to create a DATETIME that represents the first day of the month, then proceed as described above. If you do this, you should write a function that constructs a DATETIME from integer year, month, and day values, like this one from a SQL Server blog.
create function Date(@Year int, @Month int, @Day int)
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end
go
After that, the question becomes:
SELECT ... FROM ...
WHERE DateColumn >= Date(@Year,@Month,1)
AND DateColumn < DATEADD(month, 1, Date(@Year,@Month,1))
Answered by Joe
Solution #3
One more quick tip for oracle db. You might also use the to char function, as shown here:
For Month:
to_char funcion is suported by sql language and not by one specific database.
I hope to be of further assistance to anyone…
Abs!
Answered by Marcelo Rebouças
Solution #4
A regular WHERE clause will also serve as a substitute to the MONTH and YEAR functions:
select *
from yourtable
where '2009-01-01' <= datecolumn and datecolumn < '2009-02-01'
Answered by Andomar
Solution #5
Look into DATEPART if you’re using SQL Server.
http://msdn.microsoft.com/en-us/library/ms174420(SQL.90).aspx
DATEPART(mm, [THE DATE YOU’RE LOOKING AT]) DATEPART(mm, [THE DATE YOU’RE LOOKING AT]) DATEPART(mm, [
After that, you may use standard integer logic with it. The year is the same, except instead of mm, use yy.
Answered by Gromer
Post is based on https://stackoverflow.com/questions/851236/where-clause-to-find-all-records-in-a-specific-month