Coder Perfect

To find all data in a certain month, use the WHERE clause.

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