WHERE 子句查找特定月份中的所有记录

我希望能够给一个存储过程一个月和一年，并让它返回在该月发生的所有事情，我如何做到这一点，因为我不能比较，因为有些月份有不同的天数等？

最好的方法是什么? 我可以要求根据年份和月份进行比较吗？

谢谢。

496269 次浏览
小开

If you're using SQL Server, look into DATEPART.

http://msdn.microsoft.com/en-us/library/ms174420(SQL.90).aspx

DATEPART(mm, [THE DATE YOU'RE LOOKING AT])

You can then use normal integer logic with it. Same for year, just use yy instead of mm.

小开

Can I just ask to compare based on the year and month?

You can. Here's a simple example using the AdventureWorks sample database...

DECLARE @Year       INT
DECLARE @Month      INT


SET @Year = 2002
SET @Month = 6


SELECT
[pch].*
FROM
[Production].[ProductCostHistory]   pch
WHERE
YEAR([pch].[ModifiedDate]) = @Year
AND MONTH([pch].[ModifiedDate]) = @Month
小开
最佳答案

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
小开

Something like this should do it:

select * from yourtable where datepart(month,field) = @month and datepart(year,field) = @year

Alternatively you could split month and year out into their own columns when creating the record and then select against them.

Iain

小开

One way would be to create a variable that represents the first of the month (ie 5/1/2009), either pass it into the proc or build it (concatenate month/1/year). Then use the DateDiff function.

WHERE DateDiff(m,@Date,DateField) = 0

This will return anything with a matching month and year.

小开

As an alternative to the MONTH and YEAR functions, a regular WHERE clause will work too:

select *
from yourtable
where '2009-01-01' <= datecolumn and datecolumn < '2009-02-01'
小开

Using the MONTH and YEAR functions as suggested in most of the responses has the disadvantage that SQL Server will not be able to use any index there may be on your date column. This can kill performance on a large table.

I would be inclined to pass a DATETIME value (e.g. @StartDate) to the stored procedure which represents the first day of the month you are interested in.

You can then use

SELECT ... FROM ...
WHERE DateColumn >= @StartDate
AND DateColumn < DATEADD(month, 1, @StartDate)

If you must pass the month and year as separate parameters to the stored procedure, you can generate a DATETIME representing the first day of the month using CAST and CONVERT then proceed as above. If you do this I would recommend writing a function that generates a DATETIME from integer year, month, day values, e.g. the following 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

The query then becomes:

SELECT ... FROM ...
WHERE DateColumn >= Date(@Year,@Month,1)
AND DateColumn < DATEADD(month, 1, Date(@Year,@Month,1))
小开

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')
小开

More one tip very simple to oracle db. You also could use to_char function, look:

For Month:

to_char(happened_at , 'MM') = 01

For Year:
to_char(happened_at , 'YYYY') = 2009

For Day:
to_char(happened_at , 'DD') = 01

to_char funcion is suported by sql language and not by one specific database.

I hope help anybody more...

Abs!

小开 
SELECT * FROM yourtable WHERE yourtimestampfield LIKE 'AAAA-MM%';

Where AAAA is the year you want and MM is the month you want


提交答案